17,082
社区成员
发帖
与我相关
我的任务
分享
--1、union 本身就能剔重
SELECT CONFERENCE_NAME,
(SELECT MAX(TIME1)
FROM TIME1 T1
WHERE T1.CONFERENCE_ID = C.CONFERENCE_ID
UNION
SELECT MAX(TIME2)
FROM TIME2 T2
WHERE T2.CONFERENCE_ID = C.CONFERENCE_ID) MAX_TIME
FROM CONFERENCE C
--2、用分析函数来做
SELECT CONFERENCE_NAME,
GREATEST(MAX(T1.TIME1) OVER(), MAX(T2.TIME2) OVER()) MAX_TIME
FROM CONFERENCE C, TIME1 T1, TIME2 T2
WHERE T1.CONFERENCE_ID = C.CONFERENCE_ID
AND T2.CONFERENCE_ID = C.CONFERENCE_ID;
--試下
select c.conference_id,t1.time1,t2.time2
from conference c
left join (select max(time1) time1 from time1 group by conference_id) t1 on c.conference_id=t1.conference_id
left join (select max(time2) time2 from time2 group by conference_id) t2 on c.conference_id=t2.conference_id