MySQL - 计算行数和左连接问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2186054/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Counting rows and left join problem
提问by k00k
I have 2 tables, campaigns and campaign_codes:
我有 2 个表，活动和活动代码：
campaigns: id, partner_id, status
活动：id、partner_id、状态
campaign_codes: id, code, status
活动代码：ID、代码、状态
I want to get a count of all campaign_codes for all campaigns WHERE campaign_codes.status equals 0 OR where there are no campaign_codes records for a campaign.
我想获取所有活动的所有活动代码的计数，其中活动代码.status 等于 0 或没有活动的活动代码记录。
I have the following SQL, but of course the WHERE statement eliminates those campaigns which have no corresponding records in campaign_codes ( i want those campaigns with zero campaign_codes as well)
我有以下 SQL，但当然 WHERE 语句消除了那些在活动代码中没有相应记录的活动（我也希望那些活动代码为零）
SELECT
c.id AS campaign_id,
COUNT(cc.id) AS code_count
FROM
campaigns c
LEFT JOIN campaign_codes cc on cc.campaign_id = c.id
WHERE c.partner_id = 4
AND cc.status = 0
GROUP BY c.id
回答by Rowland Shaw
I'd opt for something like:
我会选择类似的东西：
SELECT
c.id AS campaign_id,
COUNT(cc.id) AS code_count
FROM
campaigns c
LEFT JOIN campaign_codes cc on cc.campaign_id = c.id
AND cc.status = 0 -- Having this clause in the WHERE, effectively makes this an INNER JOIN
WHERE c.partner_id = 4
GROUP BY c.id
Moving the ANDto the join clause makes the join succeed or fail, crucially keeping resulting rows in where there is no matching row in the 'right' table.
移动ANDto join 子句使连接成功或失败，关键是将结果行保留在“正确”表中没有匹配行的位置。
If it were in the WHERE, the comparisons to NULL (where there is no campaign_code) would fail, and be eliminated from the results.
如果它在 中WHERE，则与 NULL（没有campaign_code）的比较将失败，并从结果中删除。
回答by RedFilter
SELECT
c.id AS campaign_id,
COUNT(cc.id) AS code_count
FROM
campaigns c
LEFT JOIN campaign_codes cc on cc.campaign_id = c.id
AND c.partner_id = 4
AND cc.status = 0
GROUP BY c.id
