;with sql1 as(
select a.sysserialno,a.FailDetailReason,a.StationName, a.CreateDT,
row_number() over(partition by a.sysserialno, a.stationname order by a.createdt desc) as num
from mfsystestresult a
where a.sysserialno in('FX213050001B9','FX213060002AN')
and a.TestStatus = 'fail'),
sql2 as( select a.*,
DATEDIFF(ss, b.CreateDT, a. CreateDT) as dit
from sql1 a inner join mfsystestresult b on b.sysserialno = a.sysserialno and b.StationName = a.StationName+'_Start' where a.num = 1
),
sql3 as (select *,row_number() over(partition by a.sysserialno, a.stationname order by a.dit ) as subnum
from sql2 a where dit >= 0 )
select a.sysserialno,a.FailDetailReason,a.StationName,
CAST(CAST(a.dit / (60*60*24) AS INT) AS VARCHAR) + N'天'
+ CAST(CAST(a.dit % 86400 / 3600 AS INT) AS VARCHAR) + N'小时'
+ CAST(CAST(a.dit % 3600 / 60 AS INT) AS VARCHAR) + N'分'
+ CAST(CAST(a.dit % 60 AS INT) AS VARCHAR) + N'秒' from sql3 a where a.subnum = 1
SQL with 用法
最新推荐文章于 2023-09-22 10:20:03 发布