mysql - 从多个表中选择和求和

Question

我需要单个 SQL 查询来查询作业的 SELECT 列表，包括 SME 特定详细信息类型的 SUM()。

我有包含传输数据的数据库。表格看起来像这样:

job:
idjob | customer
1     | 45
2     | 38
3     | 15

job-detail:
iddet | idjob | type  | value
1     | 1     | range | 100
2     | 1     | range | 85
3     | 1     | range | 12
4     | 1     | price | 64
4     | 1     | price | 5
5     | 1     | note  | Some text here
6     | 2     | range | 150
7     | 2     | price | 32
8     | 2     | note  | Some text here
9     | 2     | range | 35

我需要这个输出:

idjob | customer | total_range | total_price
1     | 45       | 197         | 69
2     | 38       | 185         | 32
3     | 15       | 0           | 0

Answer 1

你可以使用带条件聚合的左连接

select a.idjob,customer,
       sum(case when type='range' then value end) as total_range,
       sum(case when type='price' then value end) as total_price
from job a 
left join job-detail b on a.idjob=b.idjob 
group by a.idjob,customer