求教一个统计语句，谢谢

[onlytiancai]

数据
a b
2005-03-05 1
2005-04-06 5

要求一条语句实现如下结果
2005-01 0
2005-02 0
2005-03 1
2005-04 5
2005-06 0
2005-07 0
2005-08 0
2005-09 0
2005-10 0
2005-11 0
2005-12 0


也就是原数据只有3、4月份的数据，统计结果要统计一年的，没有数据的月b列位零。一条语句能写成吗？

[Eric_1999]

5分？楼主果然惜分如金！！
select decode(to_char(a,'yyyy-mm'), '2005-01', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-02', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-03', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-04', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-05', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-06', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-07', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-08', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-09', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-10', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-11', b, 0),
decode(to_char(a,'yyyy-mm'), '2005-12', b, 0),
from tab

[onlytiancai]

你的结果出来肯定只有两条记录，而不是十二条。

[Eric_1999]

select sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-01', b, 0)) "1",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-02', b, 0)) "2",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-03', b, 0)) "3",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-04', b, 0)) "4",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-05', b, 0)) "5",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-06', b, 0)) "6",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-07', b, 0)) "7",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-08', b, 0)) "8",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-09', b, 0)) "9",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-10', b, 0)) "10",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-11', b, 0)) "11",
sum(decode(to_char(to_date(a, 'yyyy-mm-dd'),'yyyy-mm'), '2005-12', b, 0)) "12"
from tmp

测试过的拉！~~~~

[Eric_1999]

刚才想错了，上面的那个我测试过拉~~~加点分吧。呵呵~~~~

[onlytiancai]

我试过了再加分哦，呵呵，悄悄告诉你，我走错版了，我是sqlserver数据库。哈哈。

[Eric_1999]

不是把~~~我写的是oracle的~~

[onlytiancai]

还给你加分的，你自己看看行呀，大哥，是12行，不是12列，你写的那个我也会写。

[onlytiancai]

我骗你的，就是oracle，你写的还是不对，再写，努力哦，离拿分差的不远了哦，我去抽颗烟，你继续想哦。

[onlytiancai]

汗，跑哪儿了？怎么不给我写了呀。

[qiaozhiwei]

找个记录多于12行的表，外联接

[onlytiancai]

快弄了一下午了，好郁闷，怎么外连接呀，我不太熟悉pl sql，帮忙大概写写行吗？

[allright_flash]

我也想抽烟！

[onlytiancai]

select nvl(count(*),0) smscount ,to_char(sendtime,'yyyy-mm') month,nvl(sum(fee),0) feesum
from
(select '2005-01' Date1 from tmp union
select '2005-02' as Date1 from tmp union
select '2005-03' as Date1 from tmp union
select '2005-04' as Date1 from tmp union
select '2005-06' as Date1 from tmp union
select '2005-07' as Date1 from tmp union
select '2005-08' as Date1 from tmp union
select '2005-09' as Date1 from tmp union
select '2005-10' as Date1 from tmp union
select '2005-11' as Date1 from tmp union
select '2005-12' as Date1 from tmp ) a left join
m_sendhistory b
on
a.Date1(+)=TO_CHAR(b.sendtime,'YYYY-MM')
where deptid = 244

and to_char(sendtime,'yyyy')='2006'
group by to_char(sendtime,'yyyy-mm')
order by to_char(sendtime,'yyyy-mm')

提示无法在关联列中制定外部连接，怎么改改呀。

[onlytiancai]

各位大仙们赶紧的救救俺先，八月十五给你们送月饼去，我这儿马踩着车呢。