sql - 如何在不使用sql循环的情况下过滤日期之间的行

Question

例如，我想过滤两个日期时间之间的单个表中的行，并且这些过滤后的行应位于单个日期下

我想获取之间的所有行(2015年3月16日上午6点)和(2015年3月17日上午6点)日期时间为(2015年3月17日)日期和(2015年3月17日上午6点)和(2015年3月18日上午6点)日期时间为(2015年3月18日)日期，依此类推。

这是我的演示表

Id      Name    LogTime
1       mj      2015-03-16 01:28:03.257
2       mj      2015-03-16 05:28:03.257
3       mj      2015-03-16 06:28:03.257
4       mj      2015-03-16 18:28:03.257
5       mj      2015-03-17 01:28:06.677
6       mj      2015-03-17 06:28:06.677
7       mj      2015-03-17 16:28:07.460
8       mj      2015-03-17 07:28:03.257
9       mj      2015-03-18 01:28:08.193
10      mj      2015-03-18 05:28:03.257
11      mj      2015-03-18 06:28:03.257
12      mj      2015-03-18 18:28:03.257
13      mj      2015-03-19 01:28:06.677
14      mj      2015-03-19 06:28:06.677
15      mj      2015-03-19 16:28:07.460
16      mj      2015-03-19 07:28:03.257
17      mj      2015-03-20 01:28:08.193
18      mj      2015-03-20 05:28:03.257
19      mj      2015-03-20 06:28:03.257
20      mj      2015-03-20 18:28:03.257

下面是我正在使用的查询。

DECLARE @i INT = 1

DECLARE @from DATETIME
    ,   @to DATETIME

WHILE (@i <= 5)
BEGIN

    SET @from = CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(D, -@i, '2015-03-20'), 102) + ' 6:00:00')
    SET @to = CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(D, -@i + 1, '2015-03-20'), 102) + ' 6:00:00')

    SELECT  *,  @to AS 'FetchedOn'
    FROM Biometric
    WHERE LogTime BETWEEN @from AND @to
    ORDER BY LogTime

    SET @i = @i + 1

END

生成以下结果。

Id  Name    LogTime             FetchedOn
14  mj  2015-03-19 06:28:06.677 2015-03-20 06:00:00.000
16  mj  2015-03-19 07:28:03.257 2015-03-20 06:00:00.000
15  mj  2015-03-19 16:28:07.460 2015-03-20 06:00:00.000
17  mj  2015-03-20 01:28:08.193 2015-03-20 06:00:00.000
18  mj  2015-03-20 05:28:03.257 2015-03-20 06:00:00.000

Id  Name    LogTime             FetchedOn
11  mj  2015-03-18 06:28:03.257 2015-03-19 06:00:00.000
12  mj  2015-03-18 18:28:03.257 2015-03-19 06:00:00.000
13  mj  2015-03-19 01:28:06.677 2015-03-19 06:00:00.000

Id  Name    LogTime             FetchedOn
6   mj  2015-03-17 06:28:06.677 2015-03-18 06:00:00.000
8   mj  2015-03-17 07:28:03.257 2015-03-18 06:00:00.000
7   mj  2015-03-17 16:28:07.460 2015-03-18 06:00:00.000
9   mj  2015-03-18 01:28:08.193 2015-03-18 06:00:00.000
10  mj  2015-03-18 05:28:03.257 2015-03-18 06:00:00.000

Id  Name    LogTime             FetchedOn
3   mj  2015-03-16 06:28:03.257 2015-03-17 06:00:00.000
4   mj  2015-03-16 18:28:03.257 2015-03-17 06:00:00.000
5   mj  2015-03-17 01:28:06.677 2015-03-17 06:00:00.000

Id  Name    LogTime             FetchedOn
1   mj  2015-03-16 01:28:03.257 2015-03-16 06:00:00.000
2   mj  2015-03-16 05:28:03.257 2015-03-16 06:00:00.000

现在我想在不使用循环的情况下获得相同的结果。我正在使用 sql 2014，有其他解决方案吗？

Answer 1

这是一些代码。想法是从测试数据中获取所有可能的不同范围。这是 CTE 返回的内容:

st                      ed
2015-03-15 06:00:00.000 2015-03-16 06:00:00.000
2015-03-16 06:00:00.000 2015-03-17 06:00:00.000
2015-03-17 06:00:00.000 2015-03-18 06:00:00.000
2015-03-18 06:00:00.000 2015-03-19 06:00:00.000
2015-03-19 06:00:00.000 2015-03-20 06:00:00.000

在此之后，根据数据落在这些范围之间的条件进行简单连接:

DECLARE @t TABLE(ID INT, D DATETIME)

INSERT INTO @t VALUES
(1       ,'2015-03-16 01:28:03.257'),
(2       ,'2015-03-16 05:28:03.257'),
(3       ,'2015-03-16 06:28:03.257'),
(4       ,'2015-03-16 18:28:03.257'),
(5       ,'2015-03-17 01:28:06.677'),
(6       ,'2015-03-17 06:28:06.677'),
(7       ,'2015-03-17 16:28:07.460'),
(8       ,'2015-03-17 07:28:03.257'),
(9       ,'2015-03-18 01:28:08.193'),
(10      ,'2015-03-18 05:28:03.257'),
(11      ,'2015-03-18 06:28:03.257'),
(12      ,'2015-03-18 18:28:03.257'),
(13      ,'2015-03-19 01:28:06.677'),
(14      ,'2015-03-19 06:28:06.677'),
(15      ,'2015-03-19 16:28:07.460'),
(16      ,'2015-03-19 07:28:03.257'),
(17      ,'2015-03-20 01:28:08.193'),
(18      ,'2015-03-20 05:28:03.257'),
(19      ,'2015-03-20 06:28:03.257'),
(20      ,'2015-03-20 18:28:03.257')

;
WITH    cte
          AS ( SELECT DISTINCT
                        DATEADD(HOUR, -18, CAST(CAST(D AS DATE) AS DATETIME)) AS st ,
                        DATEADD(HOUR, 6, CAST(CAST(D AS DATE) AS DATETIME)) AS ed
               FROM     @t
             )
    SELECT  t.ID, t.D, c.ed
    FROM    cte c
            JOIN @t t ON t.D BETWEEN c.st AND c.ed

输出:

ID  D                       ed
1   2015-03-16 01:28:03.257 2015-03-16 06:00:00.000
2   2015-03-16 05:28:03.257 2015-03-16 06:00:00.000
3   2015-03-16 06:28:03.257 2015-03-17 06:00:00.000
4   2015-03-16 18:28:03.257 2015-03-17 06:00:00.000
5   2015-03-17 01:28:06.677 2015-03-17 06:00:00.000
6   2015-03-17 06:28:06.677 2015-03-18 06:00:00.000
7   2015-03-17 16:28:07.460 2015-03-18 06:00:00.000
8   2015-03-17 07:28:03.257 2015-03-18 06:00:00.000
9   2015-03-18 01:28:08.193 2015-03-18 06:00:00.000
10  2015-03-18 05:28:03.257 2015-03-18 06:00:00.000
11  2015-03-18 06:28:03.257 2015-03-19 06:00:00.000
12  2015-03-18 18:28:03.257 2015-03-19 06:00:00.000
13  2015-03-19 01:28:06.677 2015-03-19 06:00:00.000
14  2015-03-19 06:28:06.677 2015-03-20 06:00:00.000
15  2015-03-19 16:28:07.460 2015-03-20 06:00:00.000
16  2015-03-19 07:28:03.257 2015-03-20 06:00:00.000
17  2015-03-20 01:28:08.193 2015-03-20 06:00:00.000
18  2015-03-20 05:28:03.257 2015-03-20 06:00:00.000