一个略微复杂的Sql行转列实例

这里写图片描述
表结构如上,RoomId为房间号,IsPresent为1表示该房间有人,IsPresent为0表示该房间没人。现在需要统计每个房间以1开始、以0结束的时间,如果有连续的1则取第一个1创建时间为开始时间,如果有连续的0则取最后一个0创建时间为结束时间。

第一步

先根据RoomId和CreateTime排序,使每个房间每次开房的开始时间和结束时间连在一起,由于数据表中的自增主键不一定连续,所以添加一列连续的编号用来判断是否存在连续的1或连续的0的情况。

select *,ROW_NUMBER()over(order by RoomId,CreateTime)px into #tem_px from DisorderedLiveRecord

临时表#tem_px中的信息如下:

这里写图片描述

第二步

如果有连续的1则取第一个1创建时间为开始时间,所以要删除连续的1中除第一条外的其他记录;
如果有连续的0则取最后一个0创建时间为结束时间,所以要删除连续的0中除最后一条外的其他记录;
最终数据表中只剩下每个房间每次开房唯一的开始时间和结束时间。

查找连续的1中除第一条外其他记录的Id:
a.px=b.px+1 and a.IsPresent=b.IsPresent 说明存在连续的IsPresent值相同的情况;
a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=1 说明存在连续的IsPresent值相同且值为1的情况;
因为a.px=b.px+1,所以a.px较大,因此a.Id指的是两条IsPresent值相同的记录中位置靠后那条;
b.Id指的是两条IsPresent值相同的记录中位置靠前的那条。

select a.Id from #tem_px a 
 inner join #tem_px b on a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=1 and a.RoomId=b.RoomId

查找连续的0中除最后一条外其他记录的Id:
a.px=b.px+1 and a.IsPresent=b.IsPresent 说明存在连续的IsPresent值相同的情况;
a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=0 说明存在连续的IsPresent值相同且值为0的情况;

select b.Id from #tem_px a 
 inner join #tem_px b on a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=0 and a.RoomId=b.RoomId

第三步

根据需求删除IsPresent值相同的多余数据。

delete from #tem_px where Id in
(select a.Id from #tem_px a 
 inner join #tem_px b on a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=1 and a.RoomId=b.RoomId)
delete from #tem_px where Id in
(select b.Id from #tem_px a 
 inner join #tem_px b on a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=0 and a.RoomId=b.RoomId)

删除多余数据后,临时表#tem_px中的信息如下:
这里写图片描述

第四步

此时大家发现px列已经不连续了,所以我们需要再添加一列连续的编号用来实现结束时间列转行。

select *,ROW_NUMBER()over(order by RoomId,CreateTime)px2 into #tem_px2 from #tem_px

临时表#tem_px2中的信息如下:
这里写图片描述

第五步

把IsPresent=0的结束时间作为一个新列,放到对应的IsPresent=1的开始时间之后。
注意:临时表用完之后一定要删除。

select a.RoomId,a.CreateTime as StartTime,b.CreateTime as EndTime from #tem_px2 a
left join #tem_px2 b on a.px2=b.px2 - 1 and b.IsPresent=0
where a.IsPresent=1

drop table #tem_px
drop table #tem_px2

所得结果如下:
这里写图片描述
在对SqlServer数据库表进行复杂地处理时,最重要的是要知道自己的需求是什么,以及达到这个需求需要经过哪些步骤,然后按照这些步骤写对应的Sql语句,就自然而然地得到了自己想要的结果。

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

changuncle

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值