欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle 中列添加和不同行数的两张表合并

程序员文章站 2024-02-07 18:16:52
...

问题: Oracle 中怎么把两张行数不同的表合并成一个表? Sql 为: select s.stagename sname, sum(nworkhours) nworkhours, sum(O

问题:

Oracle 中怎么把两张行数不同的表合并成一个表?

Sql 为:

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

显示为:

Oracle 中列添加和不同行数的两张表合并

第二个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)

显示为:

Oracle 中列添加和不同行数的两张表合并

想要显示的结果为:

Oracle 中列添加和不同行数的两张表合并

核心思想:

总体的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

注意:行数少的那一张表会自动补齐行数和行数多的那一张表对应

Oracle 中列添加和不同行数的两张表合并