数据表zl内容
字段1 字段2 字段3
LA C1 55
LB C2 16
LC C1 28
LA C3 58
LD C1 59
LC C2 88
LE C5 99
省略... 要求:
字段1 唯一检索排序成列
字段2 唯一检索排序成行
字段3 为他们的交叉值
如何,才能得到如下查询结果:
最好用一条SQL语句实现
LA LB LC LD LE 合计...
C1 55 0 28 59 0 142
C2 0 16 88 0 0 104
C3 58 0 0 0 0 58
C5 0 0 0 0 99 99
...
合计 113 16 116 59 99
字段1 字段2 字段3
LA C1 55
LB C2 16
LC C1 28
LA C3 58
LD C1 59
LC C2 88
LE C5 99
省略... 要求:
字段1 唯一检索排序成列
字段2 唯一检索排序成行
字段3 为他们的交叉值
如何,才能得到如下查询结果:
最好用一条SQL语句实现
LA LB LC LD LE 合计...
C1 55 0 28 59 0 142
C2 0 16 88 0 0 104
C3 58 0 0 0 0 58
C5 0 0 0 0 99 99
...
合计 113 16 116 59 99
但明显是不固定多少数据,所以一条SQL是不可能实现的
需要写个存储过程来跑,可能还需要临时表行列转换问题
sum(decode(字段1,'LA',字段3 ,0)) LA,
sum(decode(字段1,'LB',字段3 ,0)) LB,
sum(decode(字段1,'LC',字段3 ,0)) LC,
sum(decode(字段1,'LD',字段3 ,0)) LD,
sum(decode(字段1,'LE',字段3 ,0)) LE,
sum(decode(字段1,'LA',字段3 ,0))+sum(decode(字段1,'LB',字段3 ,0))+sum(decode(字段1,'LC',字段3 ,0))+sum(decode(字段1,'LD',字段3 ,0)) +sum(decode(字段1,'LE',字段3 ,0)) 合计
from zl
group by rollup(字段2)
sum(decode(字段1,'LA',字段3 ,0)) LA,
sum(decode(字段1,'LB',字段3 ,0)) LB,
sum(decode(字段1,'LC',字段3 ,0)) LC,
sum(decode(字段1,'LD',字段3 ,0)) LD,
sum(decode(字段1,'LE',字段3 ,0)) LE,
from zl
group by rollup(字段2)时可以通过
否则提示:ORA-00923及"ORA-00907: missing right parenthesis"而由于缺少字段2的列,无法检验答案是否正确
我的测试语句
select decode(grouping(字段2),1,'合计',字段2) 字段2,
sum(decode(字段1,'LA',字段3 ,0)) LA,
sum(decode(字段1,'LB',字段3 ,0)) LB,
sum(decode(字段1,'LC',字段3 ,0)) LC,
sum(decode(字段1,'LD',字段3 ,0)) LD,
sum(decode(字段1,'LE',字段3 ,0)) LE,
sum(decode(字段1,'LA',字段3 ,0))+sum(decode(字段1,'LB',字段3 ,0))+sum(decode(字段1,'LC',字段3 ,0))+sum(decode(字段1,'LD',字段3 ,0)) +sum(decode(字段1,'LE',字段3 ,0)) 合计
from
(
select 'LA' 字段1,'C1' 字段2, 55 字段3 from dual
union all
select 'LB', 'C2', 16 from dual
union all
select 'LC' ,'C1', 28 from dual
union all
select 'LA' ,'C3' ,58 from dual
union all
select 'LD','C1',59 from dual
union all
select 'LC', 'C2', 88 from dual
union all
select 'LE', 'C5', 99 from dual
)
zl
group by rollup(字段2)
结果字段2 LA LB LC LD LE 合计C1 55 0 28 59 0 142
C2 0 16 88 0 0 104
C3 58 0 0 0 0 58
C5 0 0 0 0 99 99
合计 113 16 116 59 99 403
语句中
select 'LA' 字段1,'C1' 字段2, 55 字段3 from dual
union all
select 'LB', 'C2', 16 from dual
union all
select 'LC' ,'C1', 28 from dual
union all
select 'LA' ,'C3' ,58 from dual
union all
select 'LD','C1',59 from dual
union all
select 'LC', 'C2', 88 from dual
union all
select 'LE', 'C5', 99 from dual这段是插入测试数据用的
ORA-00907 是缺少右括号,同样是书写问题
ID 名称
C1 BOOK1
C2 BOOK2
C3 BOOK3
C4 BOOK4
C5 BOOK5使结果为:
字段2 名称 La Lb Lc Ld Le 合计
C1 BOOK1 55 0 28 59 0 142
C2 BOOK2 0 16 88 0 0 104
C3 BOOK3 58 0 0 0 0 58
C5 BOOK5 0 0 0 0 99 99
合计 113 16 116 59 99 403
sum(decode(字段1,'LA',字段3 ,0)) LA,
sum(decode(字段1,'LB',字段3 ,0)) LB,
sum(decode(字段1,'LC',字段3 ,0)) LC,
sum(decode(字段1,'LD',字段3 ,0)) LD,
sum(decode(字段1,'LE',字段3 ,0)) LE,
sum(decode(字段1,'LA',字段3 ,0))+sum(decode(字段1,'LB',字段3 ,0))+sum(decode(字段1,'LC',字段3 ,0))+sum(decode(字段1,'LD',字段3 ,0)) +sum(decode(字段1,'LE',字段3 ,0)) 合计
from zl,cp
where zl.字段2=cp.id
group by rollup(字段2),名称