mysql - 与 SUM 的 LEFT 连接不显示其他表中不存在的结果

Question

电台

id  | name    | region_id
1   | sation1 |   1
2   | sation2 |   1
3   | sation3 |   2
4   | sation4 |   2

expenditure_heads

id |    name
1  |    exp1
2  |    exp2
3  |    exp3
4  |    exp4
5  |    exp5

expense_details

id | expenditure_head_id | station_id | year | month | expense
1  | 1                   | 1          | 2015 | 1     | 300
2  | 2                   | 1          | 2015 | 1     | 250
3  | 3                   | 1          | 2015 | 1     | 100
4  | 4                   | 1          | 2015 | 1     | 50
5  | 5                   | 1          | 2015 | 1     | 200
6  | 1                   | 3          | 2015 | 1     | 75
7  | 2                   | 3          | 2015 | 1     | 15
8  | 3                   | 3          | 2015 | 1     | 40
9  | 4                   | 3          | 2015 | 1     | 300
10 | 5                   | 3          | 2015 | 1     | 250
11 | 1                   | 3          | 2015 | 2     | 100
12 | 2                   | 3          | 2015 | 2     | 50
13 | 3                   | 3          | 2015 | 2     | 200
14 | 4                   | 3          | 2015 | 2     | 300
15 | 5                   | 3          | 2015 | 2     | 250

我想要的结果是获得所有电台的年度报告以及我尝试过的每个支出_头的费用

SELECT exp.name AS expenditure,st.name AS station,x.totalexpense
FROM expenditure_heads AS exp
LEFT JOIN
(SELECT expenditure_head_id,station_id,SUM(expense) as totalexpense
FROM expense_details
WHERE year = '2015'
GROUP by expenditure_head_id,station_id
)
AS x ON exp.id = x.expenditure_head_id
LEFT JOIN stations AS st  ON x.station_id = st.id WHERE st.region_id=2

这仅给出费用_详细信息中 id 的车站的正确结果

expenditure | station | totalexpense
exp1        | sation3 | 175
exp2        | sation3 | 65
exp3        | sation3 | 240
exp4        | sation3 | 600
exp5        | sation3 | 500

但我想要这样的结果

expenditure | station | totalexpense
exp1        | sation3 | 175
exp2        | sation3 | 65
exp3        | sation3 | 240
exp4        | sation3 | 600
exp5        | sation3 | 500
exp1        | sation4 | 0
exp2        | sation4 | 0
exp3        | sation4 | 0
exp4        | sation4 | 0
exp5        | sation4 | 0

提前谢谢

Answer 1

修改您的查询以使用 LEFT OUTER JOIN 而不是 LEFT JOIN

SELECT exp.name AS expenditure,st.name AS station,x.totalexpense
FROM expenditure_heads AS exp
LEFT OUTER JOIN
(SELECT expenditure_head_id,station_id,SUM(expense) as totalexpense
FROM expense_details
WHERE year = '2015'
GROUP by expenditure_head_id,station_id
)
AS x ON exp.id = x.expenditure_head_id
LEFT OUTER JOIN stations AS st  ON x.station_id = st.id WHERE st.region_id=2  

您可以尝试以下查询，它可能会起作用:

SELECT exp.name AS expenditure,st.name AS station,x.totalexpense, exp_detail.expenditure_head_id, exp_detail.station_id, SUM(exp_detail.expense) AS totalexpense  
FROM expenditure_heads exp
LEFT OUTER JOIN 
expense_details exp_detail ON exp_detail.year ='2015' AND exp_detail.expenditure_head_id = exp.id
LEFT OUTER JOIN stations st ON exp_detail.station_id = st.id WHERE st.region_id=2 
GROUP by exp_detail.expenditure_head_id,exp_detail.station_id