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月 合计
项目1 20 30 20 70 80 60 90 70 100 67.50 80 35 722.50
项目2 10 20 10 60 70 50 80 60 90 57.50 80 35 622.50
......
合计 30 50 30 130 150 110 150 130 190 125 160 70 1345.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是价格数据
SQLOracleselect
项目 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
项目1 20 30 20 70 80 60 90 70 100 67.50 80 35 722.50
项目2 10 20 10 60 70 50 80 60 90 57.50 80 35 622.50
......
合计 30 50 30 130 150 110 150 130 190 125 160 70 1345.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是价格数据
SQLOracleselect
select
'合计' as xmmc,
sum(y1)as y1,sum(y2)as y2,sum(y3)as y3,sum(y4)as y4,sum(y5)as y5,sum(y6)as y6,
sum(y7)as y7,sum(y8)as y8,sum(y9)as y9,sum(y10)as y10,sum(y11)as y11,sum(y12)as y12
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
怎么样可以把这个查询出来的横向合计跟上面那条语句查询出来的数据放在一个sql里呢?也就是融合再一次· 只查询一次。
(
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 合计
--------------------------------------------------------
1 a 55 0 0 55
2 b 0 33 0 33
3 c 0 0 143 143
4 d 0 0 0 88
5 合计 55 33 143 319
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 合计
--------------------------------------------------
1 a 55 33 99 187
2 b 22 11 143 264
3 合计 77 44 242 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)