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.40 AM
   stop       12.00 PM

我想要的输出只需要每个第一次开始 and 每次停止后开始 and 最近一站

有什么解决办法吗？

这就是我想要的输出：

   id         time
   start      08.00 AM 
   stop       09.00 AM
   start      09.30 AM
   stop       11.40 AM
   stop       12.00 PM

没有任何主键或每行都有不同的 id...我想到的唯一解决方案是：

select rn, id , time
from
(select ROW_NUMBER() over (order by time) as rn, id, time from test 
where id = 'start'
union
select ROW_NUMBER() over (order by time) as rn, id, time from test 
where id = 'stop'
) d
order by rn

基本上我在开始行和停止行之间进行了联合，如下所示：

 (select ROW_NUMBER() over (order by time) as rn, id, time from test 
    where id = 'start'
    union
    select ROW_NUMBER() over (order by time) as rn, id, time from test 
    where id = 'stop'
    ) d

returns:

    1   start   08.00
    2   start   11.00
    3   start   12.00
    4   start   13.00
    1   stop    09.00
    2   stop    10.00
    3   stop    14.00
    4   stop    15.00

从原始输入：

id          time
start   08.00
stop    09.00
stop    10.00
start   11.00
start   12.00
start   13.00
stop    14.00
stop    15.00

现在您只需按它们自己的行号对它们进行排序...那就是 rn.

最后你将得到：

1   start   08.00
1   stop    09.00
2   start   11.00
2   stop    10.00
3   start   12.00
3   stop    14.00
4   start   13.00
4   stop    15.00

@注意：我的示例值与您的值接近......但虚构......