如何在sql中从0和1列表中获取0和1状态列表 [英] How to get list of 0 and 1 status from group of zero and 1 list in sql

问题描述

发布一小时前

i在数据库中有记录为休闲

Posted an hour ago
i have records in database as fallows

status                 timestamp

1                         123

1                         124

0                          125

0                         126

1                         127

1                        128

1                         129

0                         130

但我想要他在同一行记录123和125，在同一行记录127和130所以如何做到这一点

but i want the records 123 and 125 in same row and 127 and 130 in same row so on how to do it

推荐答案

看起来像经典的群岛和边缘的问题。

试试这个：

Seems like a classic Islands and Gaps problem with edges.
Try this:

with changes as (
    select  status - lag(status,1,0) over (order by timestamp) change
           ,timestamp
    from    Table1
    )
,up as (
    select  timestamp
           ,row_number() over (order by timestamp) rn
    from    changes
    where   change = 1
    )
,down as (
    select  timestamp
           ,row_number() over (order by timestamp) rn
    from    changes
    where   change = -1
    )
select  u.timestamp up,d.timestamp down,u.rn rn
from    up u
join    down d on u.rn = d.rn
order by rn

这里 [ ^ ]小提琴。

这适用于SQLServer 2012和Oracle 10G，但早期版本缺少 Lag 功能。它可以通过使用rownumber和自联接来轻松实现。

因此，如果您有早期版本或其他数据库，则需要使用数据库和版本更新您的问题。



这是MySQL的更新解决方案：

Here's[^] the fiddle.
This works on SQLServer 2012 and Oracle 10G, but earlier versions lack the Lag function. It can be implemented on those easy enough using rownumber and a self join.
So if you have an earlier version or another database you'll need to update your question with database and version.

Here's an updated solution for MySQL:

SELECT  up.TIMESTAMP up,down.TIMESTAMP down,up.rn
FROM    (
    SELECT  TIMESTAMP
        ,@r1 := @r1 + 1 AS rn
    FROM    (
        SELECT  t1.timestamp
        FROM    Table1 t1 
        left OUTER JOIN Table1 t2 ON t1.timestamp = t2.timestamp + 1
        WHERE   t1.status - ifnull(t2.status,0) = 1
        ORDER BY t1.timestamp
        ) t1
        ,(SELECT @r1 := 0) r
    ) up
    ,(
        SELECT  TIMESTAMP
        ,@r2 := @r2 + 1 AS rn
    FROM    (
        SELECT  t1.timestamp
        FROM    Table1 t1 
        left OUTER JOIN Table1 t2 ON t1.timestamp = t2.timestamp + 1
        WHERE   t1.status - ifnull(t2.status,0) = -1
        ORDER BY t1.timestamp
        ) t1
        ,(SELECT @r2 := 0) r
    ) down
WHERE   up.rn = down.rn
ORDER BY up.rn

这里 [ ^ ]小提琴。

当你需要创建一个这样的装置时，这是一个合适的时间来说明我对MySQL的鄙视程度。



注意：此代码假设存在在时间戳序列中没有间隙。

如果有，你需要添加几个IfNull

Here's[^] the fiddle.
When you need to create a contraption like this it's a fitting time to say how much I despise MySQL.

Note: This code assumes that there are no gaps in the timestamp sequence.
If there are, you need to add a couple more IfNull

这篇关于如何在sql中从0和1列表中获取0和1状态列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！