一个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
小弟初用oracle，对sql语句不甚了解，麻烦各位帮忙解决下，小弟感激不尽
y1,y2,y3...y12都是我用sql语句起的别名，
t表中的yf是月份
t表中的jg是价格数据
SQL Oracle select
[解决办法]
有那么麻烦么 写个简单的例子 rollup汇总就可以了
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
[解决办法]
2个项目 统计3个月
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
[解决办法]
又看了下 lz子查询写麻烦了 直接关联 就可以过滤为空的了 下面这样 应该可以满足要求了
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)
[解决办法]
从显示的需求来看 不用id 只用显示项目和每个月的统计数量