TA
--------------------------
no year count
A 2008 100
B 2009 100
C 2010 100
D 2011 100TB
---------------------
no year count
A 2006 200
B 2007 200
C 2008 200
D 2011 200----------------------
需求
-----------
no year TaCount TbCount
A 2006 0 200
B 2007 0 200
A 2008 100 0
C 2008 0 200
B 2009 100 0
C 2010 100 0
D 2011 100 200
--------------------------
no year count
A 2008 100
B 2009 100
C 2010 100
D 2011 100TB
---------------------
no year count
A 2006 200
B 2007 200
C 2008 200
D 2011 200----------------------
需求
-----------
no year TaCount TbCount
A 2006 0 200
B 2007 0 200
A 2008 100 0
C 2008 0 200
B 2009 100 0
C 2010 100 0
D 2011 100 200
select * from (
select nvl(ta.no,tb.no) no,
nvl(ta.year ,tb.year ) year ,
nvl(ta.count ,0) TaCount,
nvl(tb.count,0) Tbountfrom ta
full outer join tb
on(ta.no = tb.no and ta.year = tb.year)
)
order by year asc
from (
select no, year, count count_a, 0 count_b
from ta
union all
select no, year, 0 count_a, count count_b
from tb )
group by no, year
order by year, no;
藐视这个不可以吧 如果年份是一样的 且no一样的话,要组合成一个呢比如我下的no是D的项
D 2011 100 200
tA和tB要组合呢
----------------2楼的好像也不行----
ta.year = tb.year 用这个条件都得不到我想要的吧
select * from(select no, year, count tacount, 0 tbcount from aunion allselect no, year, 0 tacount, tbcount from b) order by year, tacount;