MySQL如何筛选当前用户未投票/已投票帖子并统计票数？

需求回顾 #

有两张表：

• posts：存储用户发布的帖子，字段包括post_id（主键）、title、author_id、created
• votes：存储用户投票记录，字段包括vote_id（主键）、post_id、user_id、vote（枚举值'approve'/'reject'）

需要实现：

1. 分别获取当前用户（以user_id=3为例）已投票和未投票的所有帖子
2. 每条SQL中同时统计对应帖子的总投票数

正确SQL语句 #

1. 获取当前用户已投票的帖子及总票数 #

通过内关联筛选出用户3投过票的帖子，同时用子查询统计每个帖子的总投票数：

SELECT 
    p.post_id,
    p.title,
    p.author_id,
    p.created,
    v.vote AS my_vote, -- 显示当前用户对该帖子的投票类型
    (SELECT COUNT(*) FROM votes WHERE post_id = p.post_id) AS total_votes
FROM posts p
INNER JOIN votes v ON p.post_id = v.post_id
WHERE v.user_id = 3
GROUP BY p.post_id, p.title, p.author_id, p.created, v.vote;

2. 获取当前用户未投票的帖子及总票数 #

用NOT EXISTS判断用户3未对该帖子投票，同时统计总票数（无投票时显示0）：

SELECT 
    p.post_id,
    p.title,
    p.author_id,
    p.created,
    COALESCE((SELECT COUNT(*) FROM votes WHERE post_id = p.post_id), 0) AS total_votes
FROM posts p
WHERE NOT EXISTS (
    SELECT 1 FROM votes v 
    WHERE v.post_id = p.post_id AND v.user_id = 3
);

也可以用LEFT JOIN写法（效果一致）：

SELECT 
    p.post_id,
    p.title,
    p.author_id,
    p.created,
    COALESCE(v_total.total_votes, 0) AS total_votes
FROM posts p
LEFT JOIN votes v ON p.post_id = v.post_id AND v.user_id = 3
LEFT JOIN (
    SELECT post_id, COUNT(*) AS total_votes 
    FROM votes 
    GROUP BY post_id
) v_total ON p.post_id = v_total.post_id
WHERE v.user_id IS NULL;

原SQL问题说明 #

你之前的SQL两次LEFT JOIN votes但未添加有效筛选条件，无法区分用户是否投过票，还会产生重复数据。通过内关联（筛选已投票）或判断关联结果为空（筛选未投票），才能准确得到目标帖子。

内容的提问来源于stack exchange，提问作者manasGain