select time, max(case when c='A' then date else '' end)as A.data, max(case when c='B' then date else '' end)as B.data, max(case when c='C' then date else '' end)as C.data from ( select time,data,'A' as c from A union all select time,data,'B' from B union all select time,data,'C' from C) a group by time
with t1 as ( select 2 c1,'11:15' c2,9 c3 from dual union all select 3 c1,'11:30' c2,11 c3 from dual ),t2 as ( select 1 c1,'11:00' c2,6 c3 from dual union all select 2 c1,'11:15' c2,5 c3 from dual ),t3 as ( select 2 c1,'11:15' c2,8 c3 from dual )select t.c2,t1.c3,t2.c3,t3.c3 from ( select t1.c2 from t1 union select t2.c2 from t2 union select t3.c2 from t3 ) t left join t1 on t.c2 = t1.c2 left join t2 on t.c2 = t2.c2 left join t3 on t.c2 = t3.c2 c2 c3 c3 c3 --------------------------------------- 1 11:00 6 2 11:15 9 5 8 3 11:30 11
with t1 as( select '2' id,'11:15' time,'9' date1 from dual union select '3','11:30','11' from dual) ,t2 as( select '1' id,'11:00'time,'6' date1 from dual union select '2','11:15','5' from dual) , t3 as (select '2' id ,'11:15' time,'8' date1 from dual) select nvl(t2.time,t1.time),t1.date1,t2.date1,t3.date1 from t2 full join t1 on t2.time=t1.time full join t3 on t2.time=t3.time order by t2.time;
借楼上数据SELECT NVL(T1.TIME, T2.TIME, T3.TIME), T1.DATE1, T2.DATE1, T3.DATE1 FROM T1 FULL JOIN T2 ON T1.ID = T2.ID FULL JOIN T3 ON T1.ID = T2.ID ORDER BY NVL(T1.TIME, T2.TIME, T3.TIME)
能否换个思路解决问题啊。使用UNION效率不会高的啊。
3个表会有1个表的time是全的吗 能包括其他2个表的time数据 有的话 就不用union了
select time, max(case when c='A' then date else '' end)as A.data, max(case when c='B' then date else '' end)as B.data, max(case when c='C' then date else '' end)as C.data from ( select time,data,'A' as c from A union allselect time,data,'B' from B union allselect time,data,'C' from C) a group by time这种方式效率可能高些,具体要看执行计划。
全连接,只需要在time字段上加个索引就可以了。 with t1 as ( select '2' id, '11:15' time, '9' data from dual union select '3' id, '11:30' time, '11' data from dual), t2 as ( select '1' id, '11:00' time, '6' data from dual union select '2' id, '11:15' time, '5' data from dual), t3 as ( select '2' id ,'11:15' time, '8' data from dual) select nvl(t1.time,nvl(t2.time,t3.time)) time, t1.data "A.data", t2.data "B.data", t3.data "C.data" from t1 full join t2 on t1.time = t2.time full join t3 on t1.time = t3.time
我觉得这个思路可以!WITH t0 AS( SELECT 1 ID, '11:00' TIME FROM dual UNION SELECT 2 ID, '11:15' TIME FROM dual UNION SELECT 3 ID, '11:30' TIME FROM dual UNION SELECT 4 ID, '11:45' TIME FROM dual UNION SELECT 5 ID, '12:00' TIME FROM dual UNION SELECT 6 ID, '12:15' TIME FROM dual ), t1 AS( SELECT 2 ID,'11:15' TIME,'9' date1 FROM dual UNION SELECT 3,'11:30','11' FROM dual ), t2 AS( SELECT 1 ID,'11:00' TIME,'6' date1 FROM dual UNION SELECT 2,'11:15','5' FROM dual ), t3 AS( SELECT '2' ID ,'11:15' TIME,'8' date1 FROM dual ) SELECT t0.TIME,t1.date1,t2.date1,t3.date1 FROM t0 LEFT JOIN t1 on t0.TIME = t1.TIME LEFT JOIN t2 ON t0.TIME = t2.TIME LEFT JOIN t3 ON t0.TIME = t3.TIME ORDER BY t0.TIME;
select time,
max(case when c='A' then date else '' end)as A.data,
max(case when c='B' then date else '' end)as B.data,
max(case when c='C' then date else '' end)as C.data
from (
select time,data,'A' as c from A
union all
select time,data,'B' from B
union all
select time,data,'C' from C) a
group by time
with t1 as
(
select 2 c1,'11:15' c2,9 c3 from dual union all
select 3 c1,'11:30' c2,11 c3 from dual
),t2 as
(
select 1 c1,'11:00' c2,6 c3 from dual union all
select 2 c1,'11:15' c2,5 c3 from dual
),t3 as
(
select 2 c1,'11:15' c2,8 c3 from dual
)select t.c2,t1.c3,t2.c3,t3.c3
from
(
select t1.c2
from t1
union
select t2.c2
from t2
union select t3.c2
from t3
) t left join t1 on t.c2 = t1.c2
left join t2 on t.c2 = t2.c2
left join t3 on t.c2 = t3.c2 c2 c3 c3 c3
---------------------------------------
1 11:00 6
2 11:15 9 5 8
3 11:30 11
select '2' id,'11:15' time,'9' date1 from dual
union
select '3','11:30','11' from dual)
,t2 as(
select '1' id,'11:00'time,'6' date1 from dual
union
select '2','11:15','5' from dual)
, t3 as
(select '2' id ,'11:15' time,'8' date1 from dual)
select nvl(t2.time,t1.time),t1.date1,t2.date1,t3.date1
from t2 full join t1 on t2.time=t1.time
full join t3 on t2.time=t3.time
order by t2.time;
FROM T1
FULL JOIN T2 ON T1.ID = T2.ID
FULL JOIN T3 ON T1.ID = T2.ID
ORDER BY NVL(T1.TIME, T2.TIME, T3.TIME)
with
t1 as
( select '2' id, '11:15' time, '9' data from dual union
select '3' id, '11:30' time, '11' data from dual),
t2 as
( select '1' id, '11:00' time, '6' data from dual union
select '2' id, '11:15' time, '5' data from dual),
t3 as
( select '2' id ,'11:15' time, '8' data from dual) select nvl(t1.time,nvl(t2.time,t3.time)) time,
t1.data "A.data",
t2.data "B.data",
t3.data "C.data"
from t1
full join t2 on t1.time = t2.time
full join t3 on t1.time = t3.time
SELECT 1 ID, '11:00' TIME FROM dual
UNION
SELECT 2 ID, '11:15' TIME FROM dual
UNION
SELECT 3 ID, '11:30' TIME FROM dual
UNION
SELECT 4 ID, '11:45' TIME FROM dual
UNION
SELECT 5 ID, '12:00' TIME FROM dual
UNION
SELECT 6 ID, '12:15' TIME FROM dual
),
t1 AS(
SELECT 2 ID,'11:15' TIME,'9' date1 FROM dual
UNION
SELECT 3,'11:30','11' FROM dual
),
t2 AS(
SELECT 1 ID,'11:00' TIME,'6' date1 FROM dual
UNION
SELECT 2,'11:15','5' FROM dual
),
t3 AS(
SELECT '2' ID ,'11:15' TIME,'8' date1 FROM dual
)
SELECT t0.TIME,t1.date1,t2.date1,t3.date1
FROM t0
LEFT JOIN t1 on t0.TIME = t1.TIME
LEFT JOIN t2 ON t0.TIME = t2.TIME
LEFT JOIN t3 ON t0.TIME = t3.TIME
ORDER BY t0.TIME;