MariaDB 10.3视图中如何遍历用户ID替换查询硬编码值生成全量排名结果

你使用的MariaDB 10.3支持窗口函数，用窗口函数实现的方案性能远优于原相关子查询写法，逻辑完全和你的需求匹配：

SELECT
    us.foo,
    us.user_id_1,
    us.user_id_2,
    -- 如果需要兼容原查询相同foo返回相同position的逻辑，把ROW_NUMBER()换成RANK()即可
    ROW_NUMBER() OVER (PARTITION BY us.user_id_1 ORDER BY us.foo DESC) - 1 AS position
FROM my_table us
WHERE us.user_id_1 IN (SELECT id FROM users)
-- 你要求排序无要求，需要按用户+排名排序的话可以打开下面注释
-- ORDER BY us.user_id_1, position;

兼容原有子查询的写法 #

如果你要完全沿用你原来的子查询逻辑，只需要关联users表替换硬编码值即可：

SELECT
    us.foo,
    us.user_id_1,
    us.user_id_2,
    (
        SELECT COUNT(*)
        FROM my_table x
        WHERE x.foo >= us.foo
            AND x.user_id_2 = u.id
    ) AS position
FROM users u
INNER JOIN my_table us ON us.user_id_1 = u.id

说明 #

• 窗口函数写法的PARTITION BY us.user_id_1就是按照每个用户id分组计算排名，ORDER BY us.foo DESC按得分降序排序，减1是为了和你原来排名从0开始的逻辑对齐
• 如果同一个用户下有多条相同得分的记录，原查询会给这些记录返回相同的排名，把ROW_NUMBER()替换为RANK()即可保留这个特性

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