如何根据此特定日期条件筛选我的表？

kxeu7u2r 于 2021-08-09 发布在 Java

关注(0)|答案(3)|浏览(413)

我正在使用 SQL Server 2014 . 下面是表格摘录 t1 :

rownum    RoomID    ArrivalDate    DepartureDate     Name     GuestID
  1         287     2020-01-01      2020-01-09       John      600
  2         451     2020-01-09      2020-01-10       John      600
  3         458     2020-01-09      2020-01-10       John      600
  1         240     2020-03-19      2020-03-21       Alan      112
  2         159     2020-03-21      2020-03-22       Alan      112
  1         400     2020-05-01      2020-05-10       Joe       225
  2         155     2020-06-13      2020-06-18       Joe       225
  1         200     2020-07-01      2020-07-08       Smith     980
  2         544     2020-07-08      2020-07-10       Smith     980
  3         428     2020-09-01      2020-09-05       Smith     980
  ...

问题是：我需要过滤这个表，这样输出只会给我一个guest的那些行，其中他的arrivaldate（在rownum 2或3或4…）和他的departuredate（在rownum=1）之间的差值大于0。
简单地说：如果我们以客人john为例，rownum=2和rownum=3的到达日期都与rownum=1的离开日期相同；因此，我想在我的输出中完全排除他。客人艾伦也是。但是，对于guest smith，只有rownum=2需要排除的情况。
注意：此表中的所有访客都至少有一个rownum=2（即至少有2个条目）。
我的预期产出：

rownum     RoomID    ArrivalDate     DepartureDate   Name    GuestID
   1         400       2020-05-01      2020-05-10     Joe       225
   2         155       2020-06-13      2020-06-18     Joe       225
   1         200       2020-07-01      2020-07-08     Smith     980         
   3         428       2020-09-01      2020-09-05     Smith     980

我被困在如何写这个过滤器背后的逻辑。任何帮助都将不胜感激。

sql sql-server tsql

来源：https://stackoverflow.com/questions/62635486/how-to-filter-my-table-based-on-this-specific-date-criteria

3条答案

按热度按时间

sdnqo3pr1#

这里的诀窍似乎是在有匹配项时保留第一行，否则不包括任何行。可以使用窗口函数：

select t.*
from (select t.*,
             max(case when rownum = 1 then departuredate end) over (partition by guestid) as departuredate_1,             
             max(case when rownum <> 1 then arrivaldate end) over (partition by guestid) as arrivaldate_not_1
      from t1 t
     ) t
where (arrivaldate_not_1 > departuredate_1) and
      (rownum = 1 or arrivaldate > departuredate_1);

这是一把小提琴。

赞(0）回复(0）举报 2021-08-09

snz8szmq2#

请使用下面的查询并确认这是否是您所期望的，

select * from table where (ArrivalDate, Name) not in
(select DepartureDate, Name from table);

赞(0）回复(0）举报 2021-08-09

eqzww0vc3#

create table #Aridept
(
rownum int,
RoomID int,
ArrivalDate date,
DepartureDate date,
Name varchar(20),
GuestID int
)
insert into #Aridept 
    select  1     ,    287 ,    '2020-01-01',      '2020-01-09',       'John',      600
    union all select 2   ,      451 ,  '2020-01-09',      '2020-01-10','John' ,     600
    union all select 3   ,      458 ,  '2020-01-09',      '2020-01-10','John',      600
    union all select 1   ,      240 ,  '2020-03-19',      '2020-03-21','Alan',      112
    union all select 2   ,      159 ,  '2020-03-21',      '2020-03-22','Alan',      112
    union all select 1   ,      400 ,  '2020-05-01',      '2020-05-10','Joe',        225
    union all select 2   ,      155 ,  '2020-06-13',      '2020-06-18','Joe',       225
    union all select 1   ,      200 ,  '2020-07-01',      '2020-07-08','Smith',     980
    union all select 2   ,      544 ,  '2020-07-08',      '2020-07-10','Smith',     980
    union all select 3   ,      428 ,  '2020-09-01',      '2020-09-05','Smith',     980

--insert into #temp table which have depature date <> arrivedate
    select * into #temp
    from #Aridept a
    where  a.rownum>1 and ArrivalDate not in 
(select DepartureDate from #Aridept b where a.GuestID=b.guestid 
    and rownum=1 )

最终结果查询

select * from (
    select * from #Aridept Ari
    where rownum=1 and GuestID in ( select GuestID from #temp)
    union all
    select * from #temp
    )a order by GuestID, rownum

赞(0）回复(0）举报 2021-08-09

我来回答

如何根据此特定日期条件筛选我的表？

3条答案

相关问题

热门标签

最新问答