mysql - 来自两个联接表的mySQL SELECT，子查询中的数据总和

我有一个这样的表“ schLoc”

------------------------
| id | ... | ... | ... |
------------------------
| 1  | ... | ... | ... |
| 2  | ... | ... | ... |
| 3  | ... | ... | ... |
| 4  | ... | ... | ... |
| 5  | ... | ... | ... |
| .. | ... | ... | ... |
------------------------

另一个像这样的“ schLocDett”表，其中schLoc.id = schLocDett.idDoc

-------------------------------
| idDoc | qta | ... | merce   |
-------------------------------
| 1     |  1  | ... | fattLoc |
| 1     |  1  | ... | fattSrv |
| 2     |  3  | ... | fattLoc |
| 2     |  2  | ... | notcSrv |
| 2     |  2  | ... | fattSrv |
| 3     |  5  | ... | fattSrv |
| 3     |  3  | ... | notcSrv |
| 3     |  3  | ... | fattLoc |
| 3     |  7  | ... | fattLoc |
| 4     |  5  | ... | notcSrv |
| 4     |  4  | ... | fattSrv |
| 4     |  1  | ... | fattSrv |
| 5     |  1  | ... | notcSrv |
| ...   | ... | ... | ....... |
-------------------------------

我想要完整的schLoc列表，并为每个id关联的qta之和，但仅针对merce = fattLoc。如果不存在，则总和为0。
这是我期望的结果

---------------------------
| id | sumQta | ... | ... |
------------------------
| 1  | 1      | ... | ... |
| 2  | 3      | ... | ... |
| 3  | 10     | ... | ... |
| 4  | 0      | ... | ... |
| 5  | 0      | ... | ... |
| .. | ...    | ... | ... |
---------------------------

我尝试这样：

SELECT TOT.sumQta, TAB.*,
FROM schLoc AS TAB, schLocDett AS DETT,
    (SELECT idDoc, SUM(qta) AS sumQta
     FROM schLocDett
     WHERE merce='fattLoc'
     GROUP BY idDoc) AS TOT
WHERE TAB.id>0
  AND TAB.id=DETT.idDoc
  AND TAB.id=TOT.idDoc

但没有获得ID 4和5

任何建议都欢迎

最佳答案

SELECT TAB.*, COALESCE(TOT.sumQta, 0) AS sumQta
FROM schLoc TAB
LEFT JOIN
(
    SELECT idDoc, SUM(qta) AS sumQta
    FROM schLocDett
    WHERE merce = 'fattLoc'
    GROUP BY idDoc
) TOT
    ON TAB.id = TOT.idDoc

输出：

mysql - 来自两个联接表的mySQL SELECT，子查询中的数据总和-LMLPHP

演示在这里：

Rextester