Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > SQL实现根据类型对金额进行归类

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

WBOY
Release： 2016-06-07 14:57:51
Original
1649 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：
sql accomplish database type conduct
source：php.cn
Previous article：sql语句操作 Next article：省市县三级联动的SQL语句
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
Latest Articles by Author
Latest Issues
SQL Count() also requires 0 I have only one table and I want to count each entry of a tag that starts with 0. Now I tr...
From 2024-04-06 19:48:53
0
1
449
Calculate the sum of fields in another table using MySQL SQL query I have a schema like this: User table with attributes "user_id" and "userna...
From 2024-04-06 19:39:29
0
1
441
sql groupby not working, I want to group by code_branch ->select('a.statusasstatus','b.nameascustomer_name','e.nameasbranch_name','c.nameas...
From 2024-04-06 19:24:49
0
2
488
Record query under partial value conditions - SQL skill sharing I have a table called zipcode which has a column called code. The Code column contains the...
From 2024-04-06 16:01:26
0
1
395
Node.js: Unable to store SQL query results in an array I'm trying to push the results of a SQL query to an array. However, it doesn't seem to wor...
From 2024-04-06 14:14:46
0
1
373
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template