如何合并三个生成行号的SQL查询以获取创作者多维度排名？

你的JOIN方案失败主要有两个核心问题：

1. 未初始化子查询中的用户变量，导致排名计算逻辑失效
2. 若同一creator_id对应多条游戏记录，JOIN会产生笛卡尔积，结果混乱

以下提供两种针对性的解决方法：

方案一：窗口函数实现（推荐，MySQL 8.0+ 适用） #

窗口函数是标准SQL语法，比用户变量更简洁可靠，可直接在单查询内完成多维度排名计算：

SELECT
    creator_id,
    -- 按需求选择排名函数：ROW_NUMBER/RANK/DENSE_RANK
    ROW_NUMBER() OVER (ORDER BY visits DESC) AS visit_ranking,
    ROW_NUMBER() OVER (ORDER BY favorite_count DESC) AS favorite_ranking,
    ROW_NUMBER() OVER (ORDER BY up_votes DESC) AS upvote_ranking
FROM db.games;

-- 若需按创作者聚合（同一创作者有多条游戏记录），先统计再排名：
-- WITH creator_agg AS (
--     SELECT
--         creator_id,
--         SUM(visits) AS total_visits,
--         SUM(favorite_count) AS total_favorites,
--         SUM(up_votes) AS total_upvotes
--     FROM db.games
--     GROUP BY creator_id
-- )
-- SELECT
--     creator_id,
--     ROW_NUMBER() OVER (ORDER BY total_visits DESC) AS visit_ranking,
--     ROW_NUMBER() OVER (ORDER BY total_favorites DESC) AS favorite_ranking,
--     ROW_NUMBER() OVER (ORDER BY total_upvotes DESC) AS upvote_ranking
-- FROM creator_agg;

排名函数说明： #

• ROW_NUMBER()：和你原逻辑一致，每条记录分配唯一连续名次
• RANK()：并列排名会跳过后续名次（如两个第1名，下一个为第3名）
• DENSE_RANK()：并列排名不跳过后续名次（如两个第1名，下一个为第2名）

方案二：兼容MySQL 5.x的用户变量方案 #

如果你的MySQL版本低于8.0，需修正变量初始化逻辑，同时用LEFT JOIN避免数据丢失：

SELECT
    COALESCE(u.creator_id, f.creator_id, v.creator_id) AS creator_id,
    u.upvote_ranking,
    f.favorite_ranking,
    v.visit_ranking
FROM (
    SELECT
        (@row_up := @row_up + 1) AS upvote_ranking,
        creator_id
    FROM db.games, (SELECT @row_up := 0) AS init
    ORDER BY up_votes DESC
) AS u
LEFT JOIN (
    SELECT
        (@row_fav := @row_fav + 1) AS favorite_ranking,
        creator_id
    FROM db.games, (SELECT @row_fav := 0) AS init
    ORDER BY favorite_count DESC
) AS f ON u.creator_id = f.creator_id
LEFT JOIN (
    SELECT
        (@row_vis := @row_vis + 1) AS visit_ranking,
        creator_id
    FROM db.games, (SELECT @row_vis := 0) AS init
    ORDER BY visits DESC
) AS v ON u.creator_id = v.creator_id
-- 补全仅在部分排名中出现的创作者
UNION ALL
SELECT f.creator_id, NULL, f.favorite_ranking, NULL
FROM (
    SELECT creator_id, (@row_fav2 := @row_fav2 + 1) AS favorite_ranking
    FROM db.games, (SELECT @row_fav2 := 0) AS init
    ORDER BY favorite_count DESC
) AS f
WHERE f.creator_id NOT IN (SELECT creator_id FROM db.games)
UNION ALL
SELECT v.creator_id, NULL, NULL, v.visit_ranking
FROM (
    SELECT creator_id, (@row_vis2 := @row_vis2 + 1) AS visit_ranking
    FROM db.games, (SELECT @row_vis2 := 0) AS init
    ORDER BY visits DESC
) AS v
WHERE v.creator_id NOT IN (SELECT creator_id FROM db.games);

关键修正点： #

• 每个子查询内通过(SELECT @xxx := 0) AS init初始化变量，确保排名从1开始
• 用LEFT JOIN替代JOIN，避免丢失仅在单一排名维度存在的创作者
• 用COALESCE处理NULL值，保证creator_id字段始终有值

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