作者:winnie198808616 | 来源:互联网 | 2014-06-12 08:35
Oracle中列添加和不同行数的两张表合并问题:怎么把两张行数不同的表合并成一个表?Sql为:www.2cto.comselects.stagenamesname,sum(nworkhours)nworkhours,sum(OVERHOURS)OVERHOURS,sum(nwo...
问题:
怎么把两张行数不同的表合并成一个表?
Sql 为: www.2cto.com
select s.stagename sname,
sum(nworkhours) nworkhours,
sum(OVERHOURS) OVERHOURS,
sum(nworkhours+OVERHOURS)
from T_WorkDetails w,
t_stage s
where w.stageid=s.stageid and w.projectid='D0927380468F4A4EE04010AC0C051F15'
group by s.stagename
ORDER by s.stagename
显示为:
第二个SQL 为:
select sum(total) as temptal from (select s.stagename sname,
sum(nworkhours) nworkhours,
sum(OVERHOURS) OVERHOURS,
sum(nworkhours+OVERHOURS) total
from T_WorkDetails w,
t_stage s
where w.stageid=s.stageid and w.projectid='D0927380468F4A4EE04010AC0C051F15'
group by s.stagename
ORDER by s.stagename)
显示为: www.2cto.com
想要显示的结果为:
核心思想:select * from (表A,表B)
总体的sql如下:
select * from (select s.stagename sname,
sum(nworkhours) nworkhours,
sum(OVERHOURS) OVERHOURS,
sum(nworkhours+OVERHOURS)
from T_WorkDetails w,
t_stage s
where w.stageid=s.stageid and w.projectid='D0927380468F4A4EE04010AC0C051F15'
group by s.stagename
ORDER by s.stagename) t1,
(select sum(total) as temptal from (select s.stagename sname,
sum(nworkhours) nworkhours,
sum(OVERHOURS) OVERHOURS,
sum(nworkhours+OVERHOURS) total
from T_WorkDetails w,
t_stage s
where w.stageid=s.stageid and w.projectid='D0927380468F4A4EE04010AC0C051F15'
group by s.stagename
ORDER by s.stagename)t2)
注意:行数少的那一张表会自动补齐行数和行数多的那一张表对应.