枢轴SQL结果

Question

我有一个称为测试的表：

创建表测试 （时间int， 事件varchar（50））;

INSERT INTO [dbo].[test]
       ([time]
       ,[event])
 VALUES
       (8,'start'),
       (9,'stop'),
       (11,'start'),
       (12,'stop'),
       (null,'start')
    select No , sum(case when event ='start' then time end) start,
    sum(case when event ='stop' then time end) stop
    From
    (select ROW_NUMBER()OVER( partition by event order by time)No,time,event
    from 
    (select isnull(time,0)as time, event from [dbo].[test] )y
     )x
    group by No

如何获得零值以最终而不是开始时排序正确的位置，并且有更好的方法来编写此查询？

The expected outcome 
 Row No  Start Stop
  1      8     9
  2      11    12
  3      Null  

Answer 1

您可以使用case将nulls移至最后一个。

尝试以下操作：

select No,
    sum(case when event = 'start' then time end) start,
    sum(case when event = 'stop' then time end) stop
from (
    select ROW_NUMBER() over (
            partition by event order by case when time is null then 1 else 0 end,
                time
            ) No,
        time,
        event
    from (
        select time,
            event
        from @test
        ) y
    ) x
group by No

demo