SQL with 用法

;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