sql—在给定的时间段内，当其他列中有两个以上不同的值时，选择值

n3h0vuf2 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(357)

我已经尝试了好几天了，但是我仍然找不到如何实现这一点，也没有找到一些想法。
例如，我有这样一个表：

+------+------------------+--------+
|NAME  |WHEN              |LOCATION|
+------+------------------+--------+
|TOM   |17/05/20 00:00:00 |ABC     |
+------+------------------+--------+
|BOB   |17/05/20 00:00:00 |ABC     |  
+------+------------------+--------+
|BOB   |17/05/20 00:00:00 |XYZ     |
+------+------------------+--------+
|BOB   |18/05/20 00:00:00 |TRD     |
+------+------------------+--------+
|SAM   |19/05/20 00:00:00 |ABC     |
+------+------------------+--------+
|TOM   |18/05/20 00:00:00 |ABC     |
+------+------------------+--------+
|TOM   |21/05/20 00:00:00 |ABC     |
+------+------------------+--------+
|SAM   |23/05/20 00:00:00 |XYZ     |
+------+------------------+--------+
|BOB   |20/05/20 00:00:00 |XYZ     |
+------+------------------+--------+

我想选择在1周（或任何给定时间段）内去过2个以上“地点”的任何“名称”。从表中可以看出，由于tom只去过一个位置，因此不应拾取他，输出应如下所示：

+------+------------------+--------+
|NAME  |WHEN              |LOCATION|
+------+------------------+--------+
|BOB   |17/05/20 00:00:00 |ABC     |  
+------+------------------+--------+
|BOB   |17/05/20 00:00:00 |XYZ     |
+------+------------------+--------+
|BOB   |18/05/20 00:00:00 |TRD     |
+------+------------------+--------+
|SAM   |19/05/20 00:00:00 |ABC     |
+------+------------------+--------+
|SAM   |23/05/20 00:00:00 |XYZ     |
+------+------------------+--------+
|BOB   |20/05/20 00:00:00 |XYZ     |
+------+------------------+--------+

sql oracle

来源：https://stackoverflow.com/questions/61967630/select-values-when-it-has-more-than-2-different-values-in-other-column-in-a-give

2条答案

按热度按时间

deikduxw1#

用下列方法尝试 count . 这是演示。

with cte as
( select
    name, when, location,
    count(distinct location) over (partition by name) as ttl
  from myTable
)

select 
  name, location, when
from cte
where ttl > 1

输出：

|NAME | LOCATION | WHEN |

* -----------------------*

| BOB   ABC       17/05 | 
| BOB   TRD       18/05 | 
| BOB   XYZ       20/05 | 
| BOB   XYZ       17/05 | 
| SAM   ABC       19/05 | 
| SAM   XYZ       23/05 | 

* -----------------------*

赞(0）回复(0）举报 2021-07-26

sd2nnvve2#

尝试下面的代码，它将帮助你

declare @from_date smalldatetime = '2020-05-17'
        ,@to_date smalldatetime ='2020-05-23' 
declare @tab as table(name varchar(50),whn smalldatetime,location varchar(50))

insert into @tab values('TOM','2020-05-17','ABC')
,('BOB','2020-05-17','ABC')
,('BOB','2020-05-17','XYZ')
,('BOB','2020-05-18','TRD')
,('SAM','2020-05-19','ABC')
,('TOM','2020-05-18','ABC')
,('TOM','2020-05-21','ABC')
,('SAM','2020-05-23','XYZ')
,('BOB','2020-05-20','XYZ')

select a.* from @tab  a
inner join
(
select name,count(distinct c.location) cnt from tab c where whn between @from_date and @to_date
group by c.name having count(distinct c.location)>1
) b
on a.name=b.name

输出

name    whn location
BOB 2020-05-17 00:00:00 ABC
BOB 2020-05-17 00:00:00 XYZ
BOB 2020-05-18 00:00:00 TRD
BOB 2020-05-20 00:00:00 XYZ
SAM 2020-05-23 00:00:00 XYZ
SAM 2020-05-19 00:00:00 ABC

赞(0）回复(0）举报 2021-07-26

我来回答

sql—在给定的时间段内，当其他列中有两个以上不同的值时，选择值

2条答案

相关问题

热门标签

最新问答