如何在状态基上动态合并行

Question

我想将上的行合并到和Location基础上。我想检查一辆车在特定地点停留多长时间。在1内，表示车辆进入特定位置，表示离开特定位置。如果车辆在特定日期进入和离开同一位置，则计算TimeLast。例如，HD和BP是车辆进出的地点。

select zs.oId,o.Number,z.Id,z.Location,zs.TimeFirst,zs.inside from zs join
z on zs.Id = z.Id join 
o on zs.oId=o.oId
where z.GroupId in (1094,1095,1096) and CONVERT(Date,TimeFirst)='2016/5/29'
order by zs.oId,zs.TimeFirst

输出

oId    Number   Id       Location            TimeFirst               Inside
1639    0664    1        Hd        2016-05-29 05:48                0
1639    0664    2        Kp        2016-05-29 18:46                1
1640    0359    1        Hd        2016-05-29 13:12                0
1640    0359    1        Hd        2016-05-29 13:42                1
1640    0359    1        Hd        2016-05-29 13:46                0
1643    0812    3        Lr        2016-05-29 11:41                0
1644    0806    4        Mn        2016-05-29 06:56                0
1644    0806    5        Bp        2016-05-29 09:54                1
1644    0806    5        Bp        2016-05-29 17:43                0

预期输出

oId    Number     Id    Location      TimeFirst                TimeLast       
1639    0664      1     Hd        2016-05-29 05:48         
1639    0664      2     Kp        2016-05-29 18:46         
1640    0359      1     Hd        2016-05-29 13:12         
1640    0359      1     Hd        2016-05-29 13:42          2016-05-29 13:46    
1643    0812      3     Lr        2016-05-29 11:41         
1644    0806      4     Mn        2016-05-29 06:56         
1644    0806      5     Bp       2016-05-29 09:54           2016-05-29 17:43    

Answer 1

在Server 2012上，您可以使用窗口聚合函数lag和lead来实现这一点，这些函数允许您将数据与上一记录或下一记录中的数据进行比较：

select   oId, Number, Id, Location, TimeFirst,
         CASE WHEN inside = 1 THEN DepartureTime END DepartureTime
from     (  select   zs.oId, o.Number, z.Id, z.Location, zs.TimeFirst, 
                     zs.inside,
                     lag(zs.inside) over (partition by o.oId, z.Id 
                                          order by zs.TimeFirst) as prev_inside,
                     lead(zs.inside) over (partition by o.oId, z.Id
                                           order by zs.TimeFirst) as next_inside,
                     lead(zs.TimeFirst) over (partition by o.oId, z.Id
                                              order by zs.TimeFirst) as DepartureTime
            from     zs 
            join     z on zs.Id = z.Id 
            join     o on zs.oId=o.oId
            where    z.GroupId in (1094,1095,1096) 
            and      CONVERT(Date,TimeFirst)='2016/5/29' 
         ) as base
where    (inside = 1 or coalesce(prev_inside,0) = 0)
order by oId, TimeFirst

SQL小提琴

Answer 2

这对你很有帮助：

select  zs.old,o.Number,z.Id,z.Name,zs.Timefirst, (select case when  zs.timefirst  in (select max(Timefirst) from zs group by zs.old ,zs.id) then zs.timefirst else '' end ) as TimeLast from zs join z on zs.Id = z.Id join o on zs.old=o.old order by zs.old,zs.Timefirst