我有表T1和T2。
T1
ID TIME1 TIME2
1001 1 10
1002 1 20
T2
ID STATUS TIME
1001 NEW 1
1001 CLOSED 10
1002 NEW 1
1002 HOLD 5
1002 CLOSED 13
如果表T2中不存在状态HOLD,我想要结果TIME2-TIME1;或者TIME2-TIME1-TIME,如果该记录的表中存在状态HOLD
1001 9 (10-1)
1002 14(20-1-5)
我最初写了SQL查询,但这不起作用,因为它为第一条记录返回NULL,而为第二条记录返回OK
SELECT T1.ID,T1.TIME2-T1.TIME1-T2.TIME
FROM T1
LEFT OUTER JOIN T2 ON T1.ID=T2.ID AND T2.STATUS='HOLD'
感谢
或简称
select
t1.id,
t1.time2-case when status='HOLD' then t2.time else 0 end-t1.time1
from
t1 left join t2 on t1.id=t2.id and t2.status='HOLD'
SELECT T1.ID
, Case
When T2.STATUS = 'HOLD' THEN T1.Time2 - T1.Time1 - T2.Time
Else T1.Time2 -T1.Time1
END
FROM T1
LEFT OUTER JOIN T2 ON T1.ID=T2.ID AND T2.STATUS='HOLD'
显然,表T2可能包含多个与T1.ID匹配的行。假设T2总是包含至少一个这样的行,那么我们可以执行:
select T1.ID, min(case when T2.STATUS<>'HOLD' then T1.TIME2-T1.TIME1
when T2.STATUS='HOLD' then T1.TIME2-T1.TIME1-T2.[TIME] end)
from T1 join T2
on T1.ID=T2.ID
group by T1.ID