小问题，大家帮思考下

[haoyuzhou009]

TA
--------------------------
no year count
A 2008 100
B 2009 100
C 2010 100
D 2011 100

TB
---------------------
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

[robin_ares]

select * from (
select nvl(ta.no,tb.no) no,
nvl(ta.year ,tb.year ) year ,
nvl(ta.count ,0) TaCount,
nvl(tb.count,0) Tbount

from ta
full outer join tb
on(ta.no = tb.no and ta.year = tb.year)
)
order by year asc

[quiettown]

这个很简单，另一种写法：

select no, year, sum(count_a) tacount, sum(count_b) tbcount
  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;   

[suncrafted]

已经解决了。帮顶

[haoyuzhou009]

[Quote=引用 2 楼 quiettown 的回复:]
这个很简单，另一种写法：
SQL codeselect no,year,sum(count_a) tacount,sum(count_b) tbcountfrom (select no,year,count count_a,0 count_bfrom taunionallselect no,year,0 count_a,count count_bfrom tb )groupby no,yearorderbyyear, no;
[/Quote]
-------------------
藐视这个不可以吧 如果年份是一样的 且no一样的话，要组合成一个呢。。。比如我下的no是D的项
D 2011 100 200
tA和tB要组合呢

[haoyuzhou009]

[Quote=引用 1 楼 robin_ares 的回复:]
select * from (
select nvl(ta.no,tb.no) no,
      nvl(ta.year ,tb.year ) year ,
nvl(ta.count ,0) TaCount,
nvl(tb.count,0)  Tbount

from ta
full outer join tb
on(ta.no = tb.no and ta.year = tb.year)
)
order by year asc

[/Quote]

----------------2楼的好像也不行----
ta.year = tb.year 用这个条件都得不到我想要的吧

[haoyuzhou009]

[Quote=引用 2 楼 quiettown 的回复:]
这个很简单，另一种写法：
SQL codeselect no,year,sum(count_a) tacount,sum(count_b) tbcountfrom (select no,year,count count_a,0 count_bfrom taunionallselect no,year,0 count_a,count count_bfrom tb )groupby no,yearorderbyyear, no;
[/Quote]

呃 不好意思 刚才看茬了 你的是对的 呵呵
明天我测试下给分哈~

[白发程序猿]

确实是小问题

[inthirties]

no year TaCount TbCount

我也写个
select * from

（select no, year, count tacount, 0 tbcount from a

union all

select no, year, 0 tacount, tbcount from b） order by year, tacount;