sql - 如何使用下一行和上一行选择两个日期之间的行

Question

我有一个具有以下结构的表

ID  PersonID    Date
---------------------------
1   1           2017-04-01
2   1           2017-04-15
3   1           2017-05-13
4   1           2017-06-15
5   1           2017-08-13
6   1           2017-10-02
7   2           2017-05-04
8   2           2017-09-16
9   3           2017-04-23
10  3           2017-07-06
11  4           2017-06-01

我想选择 2017-05-01 和 2017-08-26 之间的行以及每个 PersonID 下一行和上一行(如果存在)。

我想要这样的结果:

ID  PersonID    Date
------------------------------
2   1           2017-04-15
3   1           2017-05-13
4   1           2017-06-15
5   1           2017-08-13
6   1           2017-10-02
7   2           2017-05-04
8   2           2017-09-16
9   3           2017-04-23
10  3           2017-07-06
11  4           2017-06-01

Answer 1

尝试下面的查询

create table TestData(ID int,PersonID int,[Date] date)

insert TestData(ID,PersonID,[Date])values
(1 ,1,'20170401'),(2 ,1,'20170415'),(3 ,1,'20170513'),
(4 ,1,'20170615'),(5 ,1,'20170813'),(6 ,1,'20171002'),
(7 ,2,'20170504'),(8 ,2,'20170916'),(9 ,3,'20170423'),
(10,3,'20170706'),(11,4,'20170601')
----------------
DECLARE
  @FromDate date='20170501',
  @ToDate date='20170826'

SELECT *
FROM
  (
    SELECT
      *,
      LAG(IIF([Date] BETWEEN @FromDate AND @ToDate,1,0))OVER(PARTITION BY PersonID ORDER BY [Date],ID) LagOK,
      LEAD(IIF([Date] BETWEEN @FromDate AND @ToDate,1,0))OVER(PARTITION BY PersonID ORDER BY [Date],ID) LeadOK
    FROM TestData
  ) q
WHERE ([Date] BETWEEN @FromDate AND @ToDate OR LagOK=1 OR LeadOK=1)

具有 CTE 和 ROW_NUMBER 的变体

;WITH numCTE AS(
  SELECT
    *,
    ROW_NUMBER()OVER(PARTITION BY PersonID ORDER BY [Date],ID) N
  FROM TestData
)
SELECT n.*
FROM
  (
    SELECT PersonID,MIN(N)-1 MinN,MAX(N)+1 MaxN
    FROM numCTE
    WHERE [Date] BETWEEN @FromDate AND @ToDate
    GROUP BY PersonID
  ) q
JOIN numCTE n on n.PersonID=q.PersonID AND n.N BETWEEN q.MinN AND q.MaxN

我也为这个案例添加了新的测试数据和修改的查询

create table TestData(ID int,PersonID int,[Date] date)

insert TestData(ID,PersonID,[Date])values
(1 ,1,'20170401'),(2 ,1,'20170415'),(3 ,1,'20170513'),
(4 ,1,'20170615'),(5 ,1,'20170813'),(6 ,1,'20171002'),
(7 ,2,'20170504'),(8 ,2,'20170916'),(9 ,3,'20170423'),
(10,3,'20170706'),(11,4,'20170601'),
(14,6,'20170415'),(15,6,'20170913'),(16,6,'20171015') -- new test data

DECLARE
  @FromDate date='20170501',
  @ToDate date='20170826'

SELECT *
FROM
  (
    SELECT
      *,
      LAG(IIF([Date] BETWEEN @FromDate AND @ToDate,1,0))OVER(PARTITION BY PersonID ORDER BY [Date],ID) LagOK,
      LEAD(IIF([Date] BETWEEN @FromDate AND @ToDate,1,0))OVER(PARTITION BY PersonID ORDER BY [Date],ID) LeadOK
    FROM
      (
        SELECT ID,PersonID,[Date]
        FROM TestData

        UNION ALL

        SELECT DISTINCT NULL,PersonID,@FromDate -- add phantom rows for some people
        FROM TestData p
        WHERE NOT EXISTS(SELECT * FROM TestData d WHERE d.[Date] BETWEEN @FromDate AND @ToDate AND d.PersonID=p.PersonID)
      ) q
  ) q
WHERE ([Date] BETWEEN @FromDate AND @ToDate OR LagOK=1 OR LeadOK=1)
  AND ID IS NOT NULL -- exclude phantom rows from result

还有一个带有 CTE 和 ROW_NUMBER 的新变体

;WITH numCTE AS(
  SELECT
    *,
    ROW_NUMBER()OVER(PARTITION BY PersonID ORDER BY [Date],ID) N
  FROM
    (
      SELECT ID,PersonID,[Date]
      FROM TestData

      UNION ALL

      SELECT DISTINCT NULL,PersonID,@FromDate -- add phantom rows for some people
      FROM TestData p
      WHERE NOT EXISTS(SELECT * FROM TestData d WHERE d.[Date] BETWEEN @FromDate AND @ToDate AND d.PersonID=p.PersonID)
    ) q
)
SELECT n.*
FROM
  (
    SELECT PersonID,MIN(N)-1 MinN,MAX(N)+1 MaxN
    FROM numCTE
    WHERE [Date] BETWEEN @FromDate AND @ToDate
    GROUP BY PersonID
  ) q
JOIN numCTE n on n.PersonID=q.PersonID AND n.N BETWEEN q.MinN AND q.MaxN
WHERE ID IS NOT NULL -- exclude phantom rows from result