100分求一sql语句,该如何解决
日期：2014-05-17 浏览次数：20728 次
100分求一sql语句
最好效率能高一点，因为每个表的数据量比较大。
非常感谢
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
表A
ID time data
2 11:15 9
3 11:30 11
表B
ID time data
1 11:00 6
2 11:15 5
表C
ID time data
2 11:15 8
合并成如下
time A.data B.data C.data
11:00 6
11:15 9 5 8
11:30 11
最好效率能高一点，因为每个表的数据量比较大。
非常感谢
------解决方案--------------------
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(
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
