SQL实现根据类型对金额进行归类-mysql教程-PHP中文网

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

php中文网

发布： 2016-06-07 14:57:51

原创

1802人浏览过

数据库：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构建一个临时表，然后再使用聚合完成。想问一下有没有更好的方法，这样速度应该不会很快的。请大神帮忙！！！！

select
temp.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_amount
from 
  (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 table t
  where t.type ='D'
  group by t.id) temp

登录后复制