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
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
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测试过的拉!~~~~
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 = 244and to_char(sendtime,'yyyy')='2006'
group by to_char(sendtime,'yyyy-mm')
order by to_char(sendtime,'yyyy-mm')提示无法在关联列中制定外部连接,怎么改改呀。