Home > Database > Mysql Tutorial > body text

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

WBOY
Release: 2016-06-07 14:57:51
Original
1618 people have browsed it

数据库: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构建一个临时表,然后再使用聚合完成。想问一下有没有更好的方法,这样速度应该不会很快的。请大神帮忙!!!!
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
Copy after login
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template