ReimburseID相同的資料后一條時間[AuditTime]減上一條資料的時間[AuditTime]按幾天幾時幾分顯示
uj5u.com熱心網友回復：
lag , lead 這兩個函式uj5u.com熱心網友回復：
if object_id('tempdb..#WFAudiOpinions')is not null drop table #WFAudiOpinions
create table #WFAudiOpinions(ID int identity (1,1),ReimburseID int,AuditTime datetime)
insert into #WFAudiOpinions(ReimburseID,AuditTime)
select '1','2019-06-12 10:30:39' union all
select '1','2019-06-12 12:03:17' union all
select '1','2019-06-12 12:14:33' union all
select '1','2019-06-12 12:46:50' union all
select '1','2019-06-12 12:54:46' union all
select '1','2019-06-12 12:54:54' union all
select '1','2019-06-14 04:33:54' union all
select '5','2019-06-12 23:49:34' union all
select '2','2019-06-12 23:53:06' union all
select '3','2019-06-13 09:00:32'
--方法一：支持SqlServer2012及以上版本
select ID,ReimburseID,AuditTime,
cast(minute/60/24 as nvarchar(50))+'天'+cast(minute/60%24 as nvarchar(50))+'時'+cast(minute%60 as nvarchar(50))+'分'
from (
select ID,ReimburseID,AuditTime,Previous_AuditTime,
(case when ReimburseID=Previous_ReimburseID then datediff(minute, AuditTime,Previous_AuditTime) else '' end) as minute
from(
select *,
lead(AuditTime,1) over(order by ReimburseID) as Previous_AuditTime,
lead(ReimburseID,1) over(order by ReimburseID) as Previous_ReimburseID
from #WFAudiOpinions
)t
)tt
--方法二：支持SqlServer2005及以上版本
if object_id('tempdb..#tab')is not null drop table #tab
select row_number()over(partition by ReimburseID order by AuditTime)as num, * into #tab from #WFAudiOpinions
select ID,ReimburseID,AuditTime,
cast(minute/60/24 as nvarchar(50))+'天'+cast(minute/60%24 as nvarchar(50))+'時'+cast(minute%60 as nvarchar(50))+'分'
from (
select a.ID,a.ReimburseID,a.AuditTime,b.AuditTime as Previous_AuditTime,
datediff(minute, a.AuditTime,b.AuditTime) as minute
from #tab a
left join #tab b on a.ReimburseID=b.ReimburseID and a.num=(b.num-1)
)t
uj5u.com熱心網友回復：
樓上的方法很棒，自己想了很笨的方法，一點一點計算天時分，，，，學習了學習了
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/34470.html
標籤：疑難問題
上一篇：使用變數查詢“result”表中成績小于80，且課程號為“0102002”的書學生學號
下一篇：dashen