如何用SQL按(关联表正向记录数-负向记录数)排序查询主表数据

需求说明 #

查询主表tableone的所有记录，按**关联表tabletwo中点赞数（positive=true）减去点踩数（positive=false）**的结果降序排列，场景类似投票系统：tableone为可投票实体，tabletwo中positive=1代表点赞，positive=0代表点踩。

数据库结构 #

tableone #

tabletwo #

原SQL问题分析 #

你尝试的SQL通过两次LEFT JOIN分别提取点赞和点踩记录，再用COUNT计算差值，但这种写法会导致笛卡尔积：当一个实体同时有点赞和点踩记录时，连接后的行数是点赞数×点踩数，COUNT统计的是连接后的行数而非实际的投票数量，最终计算结果错误。

优化方案 #

方案1：条件聚合（推荐，更简洁） #

直接关联tabletwo，通过CASE WHEN将点赞转为+1、点踩转为-1，再求和得到最终得分：

SELECT 
    t1.*,
    COALESCE(SUM(CASE WHEN t2.positive = 1 THEN 1 ELSE -1 END), 0) AS vote_score
FROM tableone t1
LEFT JOIN tabletwo t2 ON t1.id = t2.related_tableone_id
GROUP BY t1.id, t1.data
ORDER BY vote_score DESC;

说明

• CASE WHEN：将每条投票记录转换为+1（点赞）或-1（点踩），求和后直接得到「点赞数-点踩数」
• COALESCE(..., 0)：处理无投票记录的实体（如id=0），将SUM返回的NULL转为0
• GROUP BY：按tableone的主键和其他字段分组（兼容多数SQL模式）

方案2：子查询预统计 #

先在子查询中计算每个实体的投票得分，再与tableone关联：

SELECT 
    t1.*,
    COALESCE(t2.vote_score, 0) AS vote_score
FROM tableone t1
LEFT JOIN (
    SELECT 
        related_tableone_id,
        SUM(CASE WHEN positive = 1 THEN 1 ELSE -1 END) AS vote_score
    FROM tabletwo
    GROUP BY related_tableone_id
) t2 ON t1.id = t2.related_tableone_id
ORDER BY vote_score DESC;

说明

• 子查询先完成投票得分的统计，避免了笛卡尔积问题
• 同样用COALESCE处理无投票的实体

预期结果 #

执行上述SQL后，结果按vote_score降序排列：

内容的提问来源于stack exchange，提问作者Matti Kühler