我工作了几个小时只是为了得到我想要的SQL代码的结果，但是没有运气。我试图从一个表中选择所有记录，并从另一个表中选择匹配的记录。
请看现场样品here。
url_id short_url campaign Clicks
1 test1 campaign1 2
2 test2 campaign2 2
以上示例中的SQL代码：
SELECT u.url_id, u.short_url, c.campaign, Count(*) AS Clicks
FROM (urls u LEFT JOIN log l ON l.url_id = u.url_id) LEFT JOIN campaign c ON u.campaign_id = c.campaign_id
WHERE (((DATE_FORMAT(l.date_time, '%Y-%m-%d')) Between '2017-03-14' And '2017-03-17'))
GROUP BY u.url_id, u.short_url, c.campaign;
我想要的结果是这个。包含URL表中的所有记录。
url_id short_url campaign Clicks
1 test1 campaign1 2
2 test2 campaign2 2
3 test3 campaign1 0
4 test4 0
我试图在WHERE子句中添加
l.url_id is null，但单击的值为1而不是0（0）url_id short_url campaign Clicks
1 test1 campaign1 2
2 test2 campaign2 2
3 test3 campaign1 1
4 test4 1
以上示例中的SQL代码：
SELECT u.url_id, u.short_url, c.campaign, Count(*) AS Clicks
FROM (urls u LEFT JOIN log l ON l.url_id = u.url_id) LEFT JOIN campaign c ON u.campaign_id = c.campaign_id
WHERE (((DATE_FORMAT(l.date_time, '%Y-%m-%d')) Between '2017-03-14' And '2017-03-17')) or l.url_id is null
GROUP BY u.url_id, u.short_url, c.campaign;
最佳答案
在概念上，只在log表上聚合可能更容易，因为这是生成点击计数的原因，而不是其他表。按照这些思路，我们可以尝试以下查询：
SELECT t1.url_id,
t1.short_url,
COALESCE(t2.campaign, 'NA') AS campaign,
COALESCE(t3.num_clicks, 0) AS Clicks
FROM urls t1
LEFT JOIN campaign t2
ON t1.campaign_id = t2.campaign_id
LEFT JOIN
(
SELECT url_id, COUNT(*) AS num_clicks
FROM log
WHERE DATE_FORMAT(date_time, '%Y-%m-%d') BETWEEN '2017-03-14' AND '2017-03-17'
GROUP BY url_id
) t3
ON t1.url_id = t3.url_id;
输出：
演示：
Rextester
关于mysql - 从一个表中选择所有记录，从另一表中选择匹配的记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42829477/