SQL 选择行 [英] SQL SELECTING ROW
本文介绍了SQL 选择行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
可能的重复:
SQL SERVER 选择行
如果我有下面这张表
id time
start 08.00 AM
stop 09.00 AM
stop 09.30 AM
start 09.30 AM
start 11.00 AM
start 11.30 AM
stop 11.30 AM
stop 12.00 PM
我希望输出看起来像下面的输出:
I want output that looks like output below:
有什么解决办法吗?
这是我想要的输出:
id time end_time interval
start 08.00 AM 09.00 AM 60 minute
stop 09.00 AM 09.30 AM 30 minute
start 09.30 AM 11.30 AM 120 minute
stop 11.30 AM 12.00 PM 30 minute
stop 12.00 PM NULL NULL
推荐答案
我想要的输出只需要每次第一次启动和每次启动后停止和最近停止
I want output that only take every first start and every start after stop and the latest stop
使用 row_number()
窗口函数很容易做到这一点:
That's easily done with the row_number()
window function:
; with numbered as
(
select row_number() over (order by [time]) rn
, count(*) over() as cnt
, *
from Table1
)
select *
from numbered cur
left join
numbered prev
on cur.rn = prev.rn + 1
left join
numbered next
on cur.rn = next.rn - 1
where cur.rn in (1, cur.cnt) -- first or last row
or (cur.id = 'start' and prev.id = 'stop') -- start after stop
order by
cur.rn
并不是您的示例输出不符合您的要求.它包括几行都不符合三个条件(第一行、最后一行、停止后开始.)
Not that your example output does not meet your requirements. It includes several rows that match neither of the three criteria (first row, last row, start after stop.)
这篇关于SQL 选择行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文