时间戳

首页 > 解决方案 > 如何根据这个特定的日期条件过滤我的表格？

sql - 如何根据这个特定的日期条件过滤我的表格？

问题描述

我正在使用SQL Server 2014. 以下是 Table 的摘录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
  ...

问题：我需要过滤此表，以便输出只为我提供客人的那些行，其中他的 ArrivalDate（在 rownum 2 或 3 或 4...）和他的 DepartureDate（在 rownum = 1）之间存在差异大于 0。

简化：如果我们选择客人 John，他的 ArrivalDate for rownum=2 和 rownum=3 都与他的 DepartureDate for rownum=1 相同；因此我想在我的输出中完全排除他。客人艾伦也一样。但是，仅对于需要排除 rownum=2 的 Guest Smith。

注意：此表中的所有客人都至少有一个 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

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

标签： sqlsql-servertsql

解决方案

这里的技巧似乎是在匹配时保留第一行 - 但不包括任何其他行。您可以使用窗口函数：

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);

这是一个 db<>fiddle。

推荐阅读