SQL实现根据类型对金额进行归类

数据库：oracle 问题：在一张明细表里有一个字段表示类型，一个表示金额。现需要将几种类型的金额各合计为一种。 如现有如下数据： idtypeamount 1A100 1A20 1B20 1C10 2B150 2D50 3A50 3C10 3D20 ………… 变成 idtypyA_amounttypyB_amounttypyC_amountt

数据库：oracle

问题：在一张明细表里有一个字段表示类型，一个表示金额。现需要将几种类型的金额各合计为一种。

如现有如下数据：

id type amount

1 A 100

1 A 20

1 B 20

1 C 10

2 B 150

2 D 50

3 A 50

3 C 10

3 D 20

…………

变成

id typyA_amount typyB_amount typyC_amount typyD_amount

1 120 20 10

2 150 50

3 50 10 20

……..

我的实现是使用union all构建一个临时表，然后再使用聚合完成。想问一下有没有更好的方法，这样速度应该不会很快的。请大神帮忙！！！！ <style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms

–> <!—ecms
• $velocityCount

–> <!—ecms

–>

selecttemp.id,sum(temp.typyA_amount) as typyA_amount,sum(temp.typyB_amount) as typyB_amount,sum(temp.typyC_amount) as typyC_amount,sum(temp.typyD_amount) as typyD_amountfrom   (select t.id  ,sum(t.A) as typyA_amount  --A  ,null as typyB_amount --B  ,null as typyC_amount --C  ,null as typyD_amount --D  from table t  where t.type ='A'  group by t.id  union all  select t.id  ,null as typyA_amount  --A  ,sum(t.B) as typyB_amount --B  ,null as typyC_amount --C  ,null as typyD_amount --D  from table t  where t.type ='B'  group by t.id  union all  select t.id  ,null as typyA_amount  --A  ,null as typyB_amount --B  ,sum(t.C) as typyC_amount --C  ,null as typyD_amount --D  from table t  where t.type ='C'  group by t.id  union all  select t.id  ,null as typyA_amount  --A  ,null as typyB_amount --B  ,null as typyC_amount --C  ,sum(t.D) as typyD_amount --D  from <strong>本文来源gaodai#ma#com搞@代~码^网+</strong>table t  where t.type ='D'  group by t.id) temp

喜欢 (0)赏

[搞代码]

分享 (0)