如何在mysql中获取首次打开和关闭状态 [英] How to fetch First open and close status in mysql

问题描述

我有这样的样本数据:

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

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

How can i get the output like this :

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

我尝试使用 Row_number 但无法获取正确的结果集.

I have tried using Row_number but unable to fetch proper result set .

Select * from  (
select *,ROW_NUMBER()OVER(PARTITION BY vehicle_id,status )R from test_sam  )T
Where T.R= 1 ORDER BY id

任何人都可以建议如何进行.

Can anyone suggest how to proceed .

推荐答案

选择所有 status 与之前记录不同的记录就足够了(如果存在 - 最古老的似乎是无条件退还).

It's enough to select all records for which status is not the same as in previous record (if exists - the most ancient seems to be returned unconditionally).

WITH cte AS 
(
SELECT *, 
       COALESCE(LAG(Status) OVER (ORDER BY dt), 'unknown') prev_status
FROM test_sam
)
SELECT * 
FROM cte
WHERE Status != prev_status
ORDER BY dt;

小提琴

这篇关于如何在mysql中获取首次打开和关闭状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！