时间戳

首页 > 解决方案 > SQL，通过移位列有效地连接同一个表

sql - SQL，通过移位列有效地连接同一个表

问题描述

对于 SQL 中的以下情况，我需要一些帮助（我使用 MSSQL）。我有桌子

Col1 Col2 时间1 时间2
1 一个 2021-01-18 17:41:26.767 2021-01-20 17:54:21.383
1 b 2021-01-22 17:41:26.767 2021-01-24 17:54:21.383
2 C 2021-01-25 17:41:26.767 2021-01-27 17:54:21.383
2 d 2021-01-30 17:41:26.767 2021-01-31 17:54:21.383
3 e 2021-02-05 17:54:21.383 2021-02-15 17:54:21.383
3 F 2021-02-15 17:54:21.383 2021-02-16 17:54:21.383
3 F 2021-02-17 18:13:43.610 2021-02-18 17:54:21.383 
CREATE TABLE Test1 
(
    Col1  INT,
    Col2 CHAR(1),
    TIME1 DATETIME,
    TIME2 DATETIME
);

INSERT  INTO Test1 (Col1, Col2, Time1, Time2)
VALUES (1, 'a', GETDATE(), GETDATE() + 2), 
        (1, 'b', GETDATE() + 4, GETDATE() + 6), 
        (2, 'c', GETDATE() + 7, GETDATE() + 9), 
        (2, 'd', GETDATE() + 12, GETDATE() + 13), 
        (3, 'e', GETDATE() + 18, GETDATE() + 28),
        (3, 'f', GETDATE() + 28, GETDATE() + 29),
        (3, 'f', GETDATE() + 30, GETDATE() + 31);

我想按照以下规则加入这个表：“如果下一行的TIME2 = TIME1 或TIME2 + 下一行的 1 = TIME1 或TIME2 + 2 = TIME1 然后通过保持较早的日期分组离开加入通过 Col1。如果根据上述规则无法连接行，则保留 TIME1"。

结果应如下所示：

Col1 Col2From Col2To 时间
1 一个 b 2021-01-20 17:54:21.383
1 b 无效的 2021-01-22 17:54:21.383
2 C 无效的 2021-01-25 17:54:21.383
2 d 无效的 2021-01-30 17:54:21.383
3 e F 2021-02-15 17:41:26.767
3 F F 2021-02-16 17:54:21.383
3 F 无效的 2021-02-17 17:54:21.383

当然，我可以这样做：

select ..
from Test1 as t
left join Test1 as t1 
on t1.TIME1 = DATEADD(DAY,1,t.TIME1)
and t1.Col1 = t.Col1
left join Test1 as t2
on t2.TIME1 = DATEADD(DAY,2,t.TIME1)
and t2.Col1 = t.Col1
.
.

但这很丑陋，我相信，有一种更有效的方法可以做到这一点。最好的办法是使用参数允许灵活的白班数量。

请你帮助我好吗？提前谢谢了！

标签： sqlsql-server

解决方案

根据您的解释“保留按 Col1 分组的较早日期”，这意味着您需要保留 MIN(t.time2) 但在您的示例输出中似乎您正在显示下一次 time2 所以这就是我认为您需要做的：

 SELECT
    t.col1 
    , t.Col2 Col2From
    , CASE WHEN LEAD(t.time1) OVER (PARTITION BY col1 ORDER BY time1) IN (t.time2, DATEADD(DAY,1 , t.time2) , DATEADD(DAY,2 , t.time2))
            THEN LEAD(t.col2) OVER (PARTITION BY col1 ORDER BY time1)
            ELSE null
    END Col2To
    , CASE WHEN LEAD(t.time1) OVER (PARTITION BY col1 ORDER BY time1) IN (t.time2, DATEADD(DAY,1 , t.time2) , DATEADD(DAY,2 , t.time2))
            THEN t.time2
            ELSE t.time1
    END [Time] 
FROM
    Test1 t

推荐阅读