mysql - sql - 从多个联接表中获取匹配行的计数

Question

我正在尝试获取属于“广告”表(也属于用户)中各行的点击次数和观看次数。我还尝试按观看次数最多的广告对结果进行排序。

我的表格结构:

用户:

id name
1  James

广告:属于用户(FK:user_id)

id user_id
22 1
23 4

views:属于广告(FK:ad_id)

id ad_id
1  22
2  22
3  23

点击次数:属于广告(FK:ad_id)

id ad_id
1  22
2  22
3  23

从上面的表格中，我想要一个结果，其中包含属于 id = 1 的用户的所有广告的数据以及每个广告的点击次数和观看次数

id user_id clicks_count views_count
22 1       2            2

这就是我目前所拥有的。使用此 SQL，我只获得了一个/第一个“广告”行的正确观看次数和点击次数，但忽略了其他匹配行。

SELECT ads.id, COUNT(DISTINCT views.id) AS view_count, COUNT(DISTINCT clicks.id) AS click_count   
FROM users
INNER JOIN ads ON ads.`user_id` = users.`id`
INNER JOIN views ON ads.id = views.`ad_id`
INNER JOIN clicks ON ads.id = clicks.`ad_id`
WHERE users.`id` = 1
GROUP BY views.`ad_id`
ORDER BY `view_count` DESC

这是一个 sqlfiddle http://sqlfiddle.com/#!2/5c43c/1/0

提前致谢

Answer 1

您需要使用LEFT OUTER JOIN 才能不丢失没有点击的广告

SELECT ads.id, COUNT(DISTINCT views.id) AS view_count, COUNT(DISTINCT clicks.id) AS click_count   
    FROM users
    INNER JOIN ads ON ads.`user_id` = users.`id`
    LEFT JOIN views ON ads.id = views.`ad_id`
    LEFT JOIN clicks ON ads.id = clicks.`ad_id`
    WHERE users.`id` = 1
    GROUP BY ads.`id`
    ORDER BY `view_count` DESC