行变列，查找某一时刻附近记录

最新推荐文章于 2024-07-11 21:41:59 发布

cmlxiaogeaaa

最新推荐文章于 2024-07-11 21:41:59 发布

阅读量151

点赞数

分类专栏： SQL 文章标签： servlet 数据库 java

本文链接：https://blog.csdn.net/cmlxiaogeaaa/article/details/126546557

版权

SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

SELECT ccc.realsoetime,ccc.mac, ccc.ControlTime , sum (case ccc.soetype when 5 then 1 else 0 end ) as '零序过流跳闸' ,
sum (case ccc.soetype when 128 then 1 else 0 end ) as '开关合到分' , sum (case ccc.soetype when 133 then 1 else 0 end ) as '绝缘试验',
max (case ccc.soetype when 128 then ccc.soetime else 0 end ) as '开关合到分时间' ,
max (case ccc.soetype when 5 then ccc.soetime else 0 end ) as '零序过流跳闸时间' ,
(case when ccc.ControlTime IS NULL then '就地' else '远程' end ) as '操作方式'
from ( select [Soe_Record].*,aaa.realsoetime ,RemoteControlTryRecord.ControlTime from (
SELECT [soetime] as realsoetime ,[Soe_Record].soetype,mac
FROM [Soe_Record] where soetype=133 and mac='015220090804' and
soetime between '2022-08-16 00:00:00' and '2022-08-27 00:00:00' ) as aaa left join [Soe_Record]
on (DATEDIFF(SECOND,aaa.realsoetime,[Soe_Record].soetime) BETWEEN -1 AND 1) and [Soe_Record].mac=aaa.mac
and [Soe_Record].soetime between '2022-08-16 00:00:00' and '2022-08-27 00:00:00'
left join RemoteControlTryRecord on RemoteControlTryRecord.MACID=aaa.mac
and (DATEDIFF(SECOND,aaa.realsoetime,RemoteControlTryRecord.ControlTime) BETWEEN -10 AND 10) ) as ccc GROUP BY ccc.realsoetime,ccc.mac, ccc.ControlTime