MySQL 在这里加入最佳选择？

Question

活动：

id     name            cap
1      Campaign1       2
2      Campaign2       1
3      Campaign3       1

服务：

id     id_campaign     ip
1      1               127.0.0.1
2      1               127.0.0.1
3      2               127.0.0.1

查询的结果应显示：

campaigns_id    campaigns_name          cap    count
1               Campaign1               2      2
2               Campaign2               1      1
3               Campaign3               1      0

我正在使用此查询：

   SELECT served.id_campaign, 
          campaigns.name, 
          campaigns.cap, 
          COUNT( served.id ) AS count
     FROM campaigns
LEFT JOIN served ON campaigns.id = served.id_campaign
    WHERE served.ip =  '127.0.0.1' 
 GROUP BY served.id_campaign

我的查询正在显示这些结果（不好，未显示campaigns_id.3）：

campaigns_id    campaigns_name          cap    count
1               Campaign1               2      2
2               Campaign2               1      1

我认为如果我想包含不包含的值，我正在接近错误t 被“WHERE”语句选择，因为“served”中没有与 Campaigns.id='3' 的 WHERE 语句匹配的记录

Answer 1

新编辑：

   SELECT campaigns.id as id_campaign, 
          campaigns.name, 
          campaigns.cap, 
          COUNT( served.id ) AS count
     FROM campaigns
LEFT JOIN served ON campaigns.id = served.id_campaign
      AND served.ip =  '127.0.0.1' 
 GROUP BY campaigns.id

旧帖子：

我想出了这个查询，它会产生您想要的结果：

(SELECT served.id_campaign, 
          campaigns.name, 
          campaigns.cap, 
          COUNT( served.id ) AS count
     FROM campaigns
LEFT JOIN served ON campaigns.id = served.id_campaign
    WHERE served.ip =  '127.0.0.1' 
 GROUP BY served.id_campaign)

UNION

(SELECT id, name, cap, 0 as count 
FROM campaigns 
WHERE id <> ALL 
     (
      SELECT campaigns.id 
      FROM campaigns, served 
      WHERE campaigns.id = served.id_campaign AND served.ip = "127.0.0.1"
     )
)

Answer 2

将 where 子句更改为

WHERE served.ip =  '127.0.0.1' OR served.ip IS NULL

Answer 3

LEFT JOIN 表示从左表中取出所有记录，并从右表中取出匹配的记录。因此，served.ip 将从 id=3 开始为 NULL。
所以改变where条件就可以了

WHERE serverd.ip = '127.0.0.1' or serverd.ip IS NULL