时间戳

首页 > 解决方案 > 如何在 MYSQL 中获取打开和关闭时隙

mysql - 如何在 MYSQL 中获取打开和关闭时隙

问题描述

我有这样的样本数据：

    ID Val   Name        Dt                 Status
    1, 145, 'Test1', '2020-01-28 02:18:00', 'open'
    2, 145, 'Test1', '2020-01-28 04:10:00', 'open'
    3, 145, 'Test1', '2020-01-28 05:50:00', 'open'
    4, 145, 'Test1', '2020-01-28 05:56:00', 'close'
    5, 145, 'Test2', '2020-01-28 07:36:00', 'open'
    6, 145, 'Test2', '2020-01-28 07:42:00', 'open'
    7, 145, 'Test2', '2020-01-28 07:44:00', 'open'
    8, 145, 'Test2', '2020-01-28 07:47:00', 'close'

我怎样才能得到这样的输出：

ID Val   Name        o_Dt                 o_gate      c_Dt             c_gate
1, 145, 'Test1', '2020-01-28 02:18:00', 'open'        NULL               NULL
2, 145, 'Test1', '2020-01-28 04:10:00', 'open'        NULL               NULL
3, 145, 'Test1', '2020-01-28 05:50:00', 'open'  '2020-01-28 05:56:00', 'close'
4, 145, 'Test2', '2020-01-28 07:36:00', 'open'        NULL               NULL
5, 145, 'Test2', '2020-01-28 07:42:00', 'open'        NULL               NULL
6, 145, 'Test2', '2020-01-28 07:44:00', 'open'  '2020-01-28 07:47:00', 'close'

我尝试过不同的场景，但没有继续使用

COALESCE(LAG(Status) OVER (ORDER BY dt)

ROW_NUMBER()OVER(PARTITION BY vehicle_id,status )

没有得到确切的结果。任何人都可以对此提出建议。

以前我问过相同数据的问题，但没有得到确切的答案。

如何根据MYSQL中的时间间隔将行转置为列

标签： mysqlsql

解决方案

SELECT t1.ID, t1.Val, t1.Name, t1.Dt o_Dt, t1.Status o_gate, t2.Dt c_Dt, t2.Status c_gate
FROM test t1
LEFT JOIN test t2 ON t1.Dt < t2.Dt
                 AND t1.Name = t2.Name
                 AND t2.Status = 'close'
                 AND NOT EXISTS ( SELECT NULL
                                  FROM test t3
                                  WHERE t1.Dt < t3.Dt
                                    AND t3.Dt < t2.Dt
                                    AND t1.Name = t3.Name)
WHERE t1.Status = 'open'

小提琴

推荐阅读