如何编写MySQL查询筛选年龄<30且多属性均排前50的角色记录

嘿，这个需求我之前做项目的时候刚好碰到过，咱们分特定场景和通用场景来拆解解决～

针对你的角色数据表（字段id、age、strength、intelligence、speed），要筛选出年龄小于30岁且三个能力字段均位列前50的角色，这里提供两种常用思路：

方法1：窗口函数（推荐，灵活处理排名） #

窗口函数能直观计算每个字段的排名，适合需要精确控制排名规则的场景：

SELECT id, age, strength, intelligence, speed
FROM (
    SELECT 
        *,
        -- 按能力值降序排名，数值越高排名越靠前
        ROW_NUMBER() OVER (ORDER BY strength DESC) AS strength_rank,
        ROW_NUMBER() OVER (ORDER BY intelligence DESC) AS intelligence_rank,
        ROW_NUMBER() OVER (ORDER BY speed DESC) AS speed_rank
    FROM roles
    WHERE age < 30 -- 先过滤年龄，减少后续计算量
) ranked_roles
WHERE strength_rank <= 50 
  AND intelligence_rank <= 50 
  AND speed_rank <= 50;

排名规则调整说明： #

如果需要处理并列排名（比如两个角色力量值相同，要算同一名次），可以把ROW_NUMBER()替换成：

• RANK()：并列行共享同一排名，后续排名会跳号（例：两个第1名，下一个是第3名）
• DENSE_RANK()：并列行共享同一排名，后续排名不跳号（例：两个第1名，下一个是第2名）

方法2：IN子查询（直观易懂） #

这种思路是分别取出每个能力前50的角色ID，再取交集并过滤年龄：

SELECT *
FROM roles
WHERE age < 30
  AND id IN (SELECT id FROM roles ORDER BY strength DESC LIMIT 50)
  AND id IN (SELECT id FROM roles ORDER BY intelligence DESC LIMIT 50)
  AND id IN (SELECT id FROM roles ORDER BY speed DESC LIMIT 50);

注意：这种方法在数据量大时效率略低于窗口函数，且LIMIT 50会直接截断并列数据，如果需要包含所有并列的前50名角色，优先用窗口函数。

把上面的逻辑抽象后，适用于任意数据表中column1、column2、column3三个字段均位列前50的场景，通用代码如下：

SELECT *
FROM (
    SELECT 
        *,
        -- 可根据需求修改排序方向（DESC降序/ASC升序）
        ROW_NUMBER() OVER (ORDER BY column1 DESC) AS col1_rank,
        ROW_NUMBER() OVER (ORDER BY column2 DESC) AS col2_rank,
        ROW_NUMBER() OVER (ORDER BY column3 DESC) AS col3_rank
    FROM your_table -- 替换为你的表名
) ranked_table
WHERE col1_rank <= 50 
  AND col2_rank <= 50 
  AND col3_rank <= 50;

额外优化建议： #

• 如果表数据量较大，给column1、column2、column3单独建立索引，能大幅提升排序和查询的效率；
• 如果有额外过滤条件（比如特定场景的年龄限制），尽量放在子查询中先过滤，减少后续排名计算的数据量。

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