求无JOIN的SQL查询：按score统计不同user_id的三类数量

给定包含user_id（数值型）和score（数值型）的表（存在重复行），需为每个唯一score统计三类数据：

• 拥有该score的不同用户数
• 存在至少一个score小于当前score的不同用户数
• 存在至少一个score大于等于当前score的不同用户数

核心思路 #

先按用户聚合得到每个用户的score范围（最小、最大score）及所有拥有的score集合，再针对每个唯一score，基于用户的聚合结果完成三类统计，全程无需使用JOIN语句。

SQL实现（兼容MySQL/PostgreSQL） #

WITH user_score_info AS (
    SELECT
        user_id,
        MIN(score) AS min_score,
        MAX(score) AS max_score,
        -- MySQL用GROUP_CONCAT，PostgreSQL替换为ARRAY_AGG(DISTINCT score)
        GROUP_CONCAT(DISTINCT score) AS scores
    FROM your_table
    GROUP BY user_id
),
unique_scores AS (
    SELECT DISTINCT score FROM your_table
)
SELECT
    us.score,
    -- 拥有当前score的不同用户数
    (SELECT COUNT(*) FROM user_score_info WHERE FIND_IN_SET(us.score, scores)) AS user_ids_with_current_score,
    -- 存在score小于当前值的不同用户数
    (SELECT COUNT(*) FROM user_score_info WHERE min_score < us.score) AS user_ids_score_below,
    -- 存在score大于等于当前值的不同用户数
    (SELECT COUNT(*) FROM user_score_info WHERE max_score >= us.score) AS user_ids_score_above_or_equals
FROM unique_scores us
ORDER BY us.score;

代码说明 #

1. user_score_info：按用户维度聚合，生成每个用户的核心score信息：
   • min_score：用户的最小score，用于快速判断是否存在小于当前score的记录
   • max_score：用户的最大score，用于快速判断是否存在大于等于当前score的记录
   • scores：用户所有不重复score的拼接字符串（或数组），用于判断用户是否拥有当前score
2. unique_scores：提取表中所有不重复的score值，作为统计的基准维度
3. 主查询：针对每个唯一score，通过子查询分别计算三类统计值，逻辑简洁且避免了JOIN操作

示例验证 #

将题目中的示例数据代入后，输出结果与期望完全一致：

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