MySql：通过联合或联接按计数选择

Question

我需要一种方法让页面版主可以查看按用户在该特定页面上发布的帖子总数排序的页面成员。这些帖子存储在不同的表中，例如民意调查、下载、wiki 等（例如 15 个表）。每个都有 id、user_id、page_id、标题、内容等结构。列出页面所有用户的表称为“收藏夹”。其结构很简单，就是 id、user_id、page_id、date。

我目前有一个查询，它将所有“内容表”联合在一起，以便特定用户在整个网站上显示他们在哪个页面上的所有帖子，我可以使用 WHERE page_id = $ 轻松地将其特定于每个页面page_id）。我尝试使用该代码来执行我在主持人区域中尝试执行的操作。正如我提到的，在主持人区域中，我需要按用户的帖子总数 DESC 对用户列表进行排序。

我有这段代码，但它不起作用。

$getfans = mysql_query("SELECT DISTINCT user_id, ((SELECT id FROM wiki WHERE wiki.page_id = $page_id AND wiki.user_id = favorites.user_id)
UNION ALL
(SELECT id FROM downloads WHERE downloads.page_id = $page_id AND downloads.user_id = favorites.user_id)
UNION ALL
(SELECT id FROM polls WHERE polls.page_id = $page_id AND polls.user_id = favorites.user_id)) AS posts 
FROM favorites WHERE favorites.page_id = $page_id AND favorites.status = 0 ORDER BY posts DESC", $conn);

我尝试了其他方法，但也不起作用，但这个方法对我来说最符合逻辑，所以这是我要显示的唯一方法，

请帮忙。谢谢。

另外......

这也许是朝着正确方向迈出的一步

$getfans = mysql_query("选择不同的 user_id, (SELECT COUNT(id) 来自民意调查 WHERE page_id = $page_id AND user_id = favorites.user_id) AS 帖子来自收藏夹 WHERE favorites.page_id = $page_id AND favorites.status = 0 ORDER BY posts DESC", $conn);

当我回显 $posts 时，它为我提供了该页面的用户投票的正确计数。

但是，当我尝试执行多个表时，它只返回一个表用户

。

$getfans = mysql_query("选择不同的 user_id, SUM((SELECT COUNT(id) FROM 民意调查 WHERE page_id = $page_id AND user_id = favorites.user_id)+(从 wiki WHERE page_id = 中选择 COUNT(id) $page_id AND user_id = favorites.user_id)) AS 来自收藏夹的帖子 WHERE favorites.page_id = $page_id AND favorites.status = 0 排序依据 发布 DESC", $conn);

我找到了解决方案
如果好奇，请阅读我下面的帖子。

Answer 1

使用左连接！

SELECT 
    f.user_id, 
    COUNT(w.user_id) + COUNT(d.user_id) + COUNT(p.user_id) as PageCount
FROM 
    favorites f
    left join wiki w on
        f.page_id = w.page_id
        and f.user_id = w.user_id
    left join downloads d on
        f.page_id = d.page_id
        and f.user_id = d.user_id
    left join polls p on
        f.page_id = p.page_id
        and f.user_id = p.user_id
WHERE 
    f.page_id = $page_id 
    AND f.status = 0
GROUP BY
    f.user_id
ORDER BY PageCount DESC

如果您想坚持使用联合方法：

select
    f.user_id,
    count(x.user_id) as PageCount
from
    favorites f
    inner join (
        select user_id, page_id from wiki
        union all
        select user_id, page_id from downloads
        union all
        select user_id, page_id from polls
        union all
        select user_id, page_id from videos
    ) x on
       f.user_id = x.user_id
       and f.page_id = x.page_id
WHERE 
    f.page_id = $page_id 
    AND f.status = 0
group by
    f.user_id
ORDER BY PageCount DESC

您想使用group by进行聚合，并且count函数将忽略您遇到的任何空值，因此它将仅计算正匹配。

Answer 2

我找到了解决方案。我以为我很早就有了这个，但我想我有点偏离了。感谢所有参与的人。

$getfans = mysql_query("SELECT DISTINCT user_id,
(SELECT COUNT(id) FROM wiki WHERE page_id = $page_id AND user_id = favorites.user_id) + 
(SELECT COUNT(id) FROM downloads WHERE page_id = $page_id AND user_id = favorites.user_id) + 
(SELECT COUNT(id) FROM polls WHERE page_id = $page_id AND user_id = favorites.user_id)  
AS posts FROM favorites
WHERE favorites.page_id = $page_id
AND favorites.status = 0
ORDER BY posts DESC", $conn);

只需继续对每个新表末尾添加 + 的子查询即可。也许不是最简洁的代码，但它可以工作。

这有效。我两天都无法接受它作为答案:(

Answer 3

这是我最喜欢的 SQL 查询，对于一个表，

Select Distinct user_id, Count(user_id) as C from yourtable group by user_id 

将显示每个人的 user_id 以及他们在该表中的行数。将这些结果组合起来应该很简单。

顺便说一句，如果所有表都有相同的结构，为什么不让它们都在同一个表中呢？您可以添加一列来指示记录适用的项目类型。