mysql - 如何对数据表的每个表值求和

Question

我有四张 table ，需要帮助计算每张 table 中每个价格的总和

1 个表 skom

 _________________________________
 | id | noskom      | price_skom |
 |____|_____________|____________|
 |  1 | 1           |   10       | 
 |  2 | 2           |   30       |  
 |__3_|_3___________|___50_______|

2 table 斯科普

 ______________________________________________
 | id | noskom      | noskop     | price_skop |
 |____|_____________|____________|____________|
 |  1 | 1           |   1        |   10       |  
 |  2 | 1           |   2        |   20       |  
 |  3 | 2           |   3        |    5       | 
 |  4 | 2           |   4        |   30       |  
 |____|_____________|____________|____________|

3 表 npbm

 ___________________________________________________________
 | id | noskom      | noskop     | nonpbm     | price_npbm |
 |____|_____________|____________|____________|____________|
 |  1 | 1           |   1        |   1        |   10       | 
 |  2 | 1           |   1        |   2        |   20       |   
 |  3 | 1           |   2        |   3        |    5       | 
 |  4 | 1           |   2        |   4        |    5       | 
 |  5 | 2           |   1        |   5        |   10       |   
 |  6 | 2           |   2        |   6        |   10       |  
 |  7 | 2           |   2        |   7        |   10       |       
 |____|_____________|____________|____________|____________|

4表npbp

 ________________________________________________________________________
 | id | noskom      | noskop     | nonpbm     | nonpbp     | price_npbp |
 |____|_____________|____________|____________|____________|____________|
 |  1 | 1           |   1        |   1        |    1       |   10       | 
 |  2 | 1           |   1        |   2        |    2       |   20       |   
 |  3 | 1           |   2        |   3        |    3       |    5       | 
 |  4 | 1           |   2        |   4        |    4       |    5       | 
 |  5 | 2           |   1        |   5        |    5       |   10       |   
 |  6 | 2           |   2        |   6        |    6       |   10       |  
 |  7 | 2           |   2        |   7        |   10       |   10       |       
 |____|_____________|____________|____________|____________|____________|

我需要这样的查询帮助

 select sum(price_skom),sum(price_skop),sum(price_npbm),sum(price_npbp) from each table group by noskom 

我想要这样的结果

按 noskom 划分的表格结果组

 ________________________________________________________________
 | id | noskom      | sum_skom | sum_skop | sum_npbm | sum_npbp |
 |____|_____________|__________|__________|__________|__________|
 |  1 | 1           |   10     |   30     |    40    |   90     |  
 |  2 | 2           |   30     |   35     |    30    |   70     |      
 |__3_|_3___________|___50_____|__________|__________|__________|

帮我进行mysql查询

Answer 1

类似这样的事情:

select 
noskom, 
sum (pskom) as sum_skom, 
sum (pskop) as sum_skop, 
sum (pnpbm) as sum_npbm, 
sum (pnpbp) as sum_npbp

from
(
select noskom, sum (price_skom) as pskom, 0 as pskop, 0 as pnpbm, 0 as pnpbp from skom group by noskom
union all
select noskom, 0, sum (price_skop), 0, 0 from skop group by noskom
union all
select noskom, 0, 0, sum (price_npbm), 0 from npbm group by noskom
union all
select noskom, 0, 0, 0, sum (price_npbp) from npbp group by noskom
) as p

group by noskom