MySQL-计算行数和左联接问题 [英] MySQL - Counting rows and left join problem

问题描述

我有2个表格，广告系列和campaign_codes:

I have 2 tables, campaigns and campaign_codes:

广告活动:id，partner_id，状态

campaigns: id, partner_id, status

campaign_codes:ID，代码，状态

campaign_codes: id, code, status

我想获取所有广告系列的所有campaign_code的计数，其中campaign_codes.status等于0或没有广告系列的campaign_codes记录的地方.

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.

我有以下SQL，但是WHERE语句会消除在campaign_codes中没有相应记录的那些广告系列(我也希望这些Campaign_codes也为零的广告系列)

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)

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

推荐答案

我会选择类似的东西:

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

将AND移至join子句将使连接成功或失败，从而至关重要地将结果行保留在正确"表中没有匹配行的位置.

Moving the AND to the join clause makes the join succeed or fail, crucially keeping resulting rows in where there is no matching row in the 'right' table.

如果它在WHERE中，则与NULL(没有campaign_code的地方)的比较将失败，并从结果中删除.

If it were in the WHERE, the comparisons to NULL (where there is no campaign_code) would fail, and be eliminated from the results.

这篇关于MySQL-计算行数和左联接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！