一个sql语句，折磨了我半天.
xmmc y1 y2 y3 y4 y5 y6 y7 y8 y9 y10 y11 y12 ?
项目 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
项目120 30 20 70 80 60 90 70 100 67.50 80 35722.50
项目210 20 10 60 70 50 80 60 90 57.50 80 35622.50
......
合计30 50 30 130 150 110 150 130 190 125 160 701345.00
东拼西凑找的一段sql语句，查出来的只有1到12月的数据，没有横向合计和纵向合计的数据
select *
from (
select (select j.id from j where j.id = t.id) as id,
(select j.xmmc from j where j.id = t.id) as xmmc,
sum(decode(t.yf, '1', t.jg,0.00)) as y1,
sum(decode(t.yf, '2', t.jg,0.00)) as y2,
sum(decode(t.yf, '3', t.jg,0.00)) as y3,
sum(decode(t.yf, '4', t.jg,0.00)) as y4,
sum(decode(t.yf, '5', t.jg,0.00)) as y5,
sum(decode(t.yf, '6', t.j,0.00)) as y6,
sum(decode(t.yf, '7', t.jg,0.00)) as y7,
sum(decode(t.yf, '8', t.j,0.00)) as y8,
sum(decode(t.yf, '9', t.jg,0.00)) as y9,
sum(decode(t.yf, '10', t.j,0.00)) as y10,
sum(decode(t.yf, '11', t.jg,0.00)) as y11,
sum(decode(t.yf, '12', t.jg,0.00)) as y12
from t
group by t.id
) a
where xmmc is not null
with t1 as
(
select 'a' c1,1 c2,55c3 from dual union all
select 'b' c1,2 c2,33 c3 from dual union all
select 'c' c1,3 c2,66 c3 from dual union all
select 'c' c1,3 c2,77 c3 from dual union all
select 'd' c1,4 c2,88 c3 from dual
)
select nvl(c1,'合计') c1,
sum(decode(c2,1,c3,0)) y1,
sum(decode(c2,2,c3,0)) y2,
sum(decode(c2,3,c3,0)) y3,
sum(c3) "合计"
from t1
group by rollup(c1)
c1 y1 y2 y3 合计
--------------------
1a550055
2b033033
3c00143143
4d00088
5合计5533143319
with t1 as
(
select 'a' c1,1 c2,55c3 from dual union all
select 'a' c1,2 c2,33 c3 from dual union all
select 'a' c1,3 c2,99 c3 from dual union all
select 'b' c1,1 c2,22 c3 from dual union all
select 'b' c1,2 c2,11 c3 from dual union all
select 'b' c1,3 c2,66 c3 from dual union all
select 'b' c1,3 c2,77 c3 from dual
)
select nvl(c1,'合计') c1,
sum(decode(c2,1,c3,0)) y1,
sum(decode(c2,2,c3,0)) y2,
sum(decode(c2,3,c3,0)) y3,
sum(c3) "合计"
from t1
group by rollup(c1)
c1 y1 y2 y3 合计
--------------------------------------------------
1a553399187
2b2211143264
3合计 77 44242 451
select nvl(j.xmmc,'合计') xmmc,
sum(decode(t.yf, '1', t.jg,0.00)) as y1,
sum(decode(t.yf, '2', t.jg,0.00)) as y2,
sum(decode(t.yf, '3', t.jg,0.00)) as y3,
sum(decode(t.yf, '4', t.jg,0.00)) as y4,
sum(decode(t.yf, '5', t.jg,0.00)) as y5,
sum(decode(t.yf, '6', t.j,0.00)) as y6,
sum(decode(t.yf, '7', t.jg,0.00)) as y7,
sum(decode(t.yf, '8', t.j,0.00)) as y8,
sum(decode(t.yf, '9', t.jg,0.00)) as y9,
sum(decode(t.yf, '10', t.j,0.00)) as y10,
sum(decode(t.yf, '11', t.jg,0.00)) as y11,
sum(decode(t.yf, '12', t.jg,0.00)) as y12
sum(t.yf) as "合计"
from t , j
where t.id = j.id
group by rollup(j.xmmc)
