mysql - 如何选择评论数、投票总数以及活跃用户是否投票

Question

我在构造 MySQL 查询以返回准确的评论计数、投票总数和活跃用户投票时遇到困难。

我的 table 是

wall_posts ( id, message, username, etc )
comments   ( id, wall_id, username, text, etc )
votes      ( id, wall_id, vote (+1 or -1), username )

我的查询如下所示

SELECT
    wall_posts.*,
    COUNT( comments.wall_id ) AS comment_count,
    COALESCE( SUM( v1.vote ), 0 ) AS vote_tally,
    v2.vote 
FROM
    wall_posts 
    LEFT JOIN comments ON wall_posts.id = comments.wall_id 
    LEFT JOIN votes v1 ON wall_posts.id = v1.wall_id 
    LEFT JOIN votes v2 ON wall_posts.id = v2.wall_id AND v2.username=:username 
WHERE
    symbol =: symbol
GROUP BY
    wall_posts.id
ORDER BY
    date DESC
LIMIT 15

它始终为特定活跃用户投票返回正确的值(+1 或 -1)，如果没有投票则返回 null。如果某个项目没有评论，则总票数是正确的。如果有任何评论，则投票总和将始终等于评论数，如果有否决票，则可能带有负号，但始终等于评论数量。

我认为这显然是我连接表格的方式，但我只是不明白为什么它会复制评论计数，1000000 点给可以向我解释这一点的人:)

Answer 1

您需要在子查询中执行聚合操作。现在，您将所有表JOIN连接在一起(预聚合)。如果删除聚合(以及GROUP BY)，您将看到大量数据，但这些数据实际上没有任何意义。

相反，试试这个(注意我使用的是VIEW):

CREATE VIEW walls_posts_stats AS

SELECT
    wall_posts.id,
    COALESCE( comments_stats.comment_count, 0 ) AS comment_count,
    COALESCE( votes_stats.vote_tally, 0 ) AS vote_tally
FROM

    wall_posts

    LEFT OUTER JOIN
    (
        SELECT
            wall_id,
            COUNT(*) AS comment_count
        FROM
            comments
        GROUP BY
            wall_id
    ) AS comments_stats ON wall_posts.id = comments_stats.wall_id

    LEFT OUTER JOIN
    (
        SELECT
            wall_id,
            SUM( vote ) AS vote_tally
        FROM
            votes
        GROUP BY
            wall_id
    ) AS votes_stats ON wall_posts.id = votes_stats.wall_id

然后你可以将它与你的原始墙数据连接起来查询:

SELECT
    wall_posts.*, -- note: avoid the use of * in production queries
    stats.comment_count,
    stats.vote_tally,
    user_votes.vote
FROM
    wall_posts
    INNER JOIN walls_posts_stats AS stats ON wall_posts.id = stats.id
    LEFT OUTER JOIN
    (
        SELECT
            wall_id,
            vote
        FROM
            votes
        WHERE
            username = :username
    ) AS user_votes ON wall_posts.id = user_votes.wall_id
ORDER BY
    date DESC
LIMIT 15

假设您可以将其合并到一个大型查询中(基本上将 VIEW 主体复制+粘贴到 INNER JOINwalls_posts_stats 子句中)，但我认为这会引入可维护性问题。

虽然 MySQL 确实支持 View ，但它不支持参数化 View (也称为可组合表值函数；存储过程不可组合)，因此这就是 user_votes 子查询不在 中的原因walls_posts_stats 查看。