SQL 选择行 [英] SQL SELECTING ROW

问题描述

可能的重复:
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

SQL Fiddle 的实例.

并不是您的示例输出不符合您的要求.它包括几行都不符合三个条件(第一行、最后一行、停止后开始.)

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屋！