查找在column1、column2和column3中排名前50的所有行

Question

假设我有这样一个字符表：(我使用游戏统计数据只是因为它更容易理解)

id    age  strength intelligence speed
 1    int    int       int        int
 2    int    int       int        int
 3    int    int       int        int
 4    int    int       int        int
 5    int    int       int        int
 6    int    int       int        int
 7    int    int       int        int
...   ...    ...       ...        ...
999   int    int       int        int

如何编写MySQL查询来查找所有30岁以下的字符，这些字符在强度上排名前50，在智力上排名前50，在速度上排名前50？

棘手的部分是，这个查询不会是硬编码的，它将由前端的代码构建，所有的标准都是动态的，所以我希望有一个平面和可扩展的查询，更好的没有许多嵌套连接，其中x.id = y.id = z.id部件在每个连接中都会发生变化，这是很难编码的。

我花了好几个小时看医生，但做得不好。我尝试了id，但是在这种情况下，MySQL不支持它。我也读过关于存在的文章，但是docs说它的性能真的很差。

Answer 1

Spencer7593的答案是好的，但是由于您想要“平面和可伸缩”并且只有一个表，所以使用use ()而不是ON是个好主意：

SELECT *
FROM (SELECT id FROM mytable ORDER BY strength DESC LIMIT 50)
t1
JOIN (SELECT id FROM mytable ORDER BY intelligence DESC LIMIT 50)
t2 USING (id)
JOIN (SELECT id FROM mytable ORDER BY speed DESC LIMIT 50)
t3 USING (id)
JOIN mytable USING (id)
WHERE age < 30;

这样就很容易将请求从前端编码到查询中。

Answer 2

该规范没有明确什么是“排名前50”。

假设这意味着按照strength的顺序获得所有行的前50行，并丢弃/排除其余的行.

我建议使用一系列内联视图来获得50个id值，并将这些集合连接起来以获得交集.

 SELECT t.id
      , t.age
      , t.strength
      , t.intelligence
      , t.strength
   FROM ( SELECT qq.id 
            FROM mytable qq
           ORDER BY qq.strength DESC 
           LIMIT 50
        ) q
   JOIN ( SELECT rr.id 
            FROM mytable rr
           ORDER BY rr.intelligence DESC 
           LIMIT 50
        ) r
     ON r.id = q.id
   JOIN ( SELECT ss.id 
            FROM mytable ss
           ORDER BY ss.speed DESC 
           LIMIT 50
        ) s
     ON s.id = q.id 
   JOIN mytable t
     ON t.id = q.id
  WHERE t.age < 30   
  ORDER BY ... 

为了获得最好的性能，我们需要适当的索引，以避免昂贵的“使用文件短”操作。

... ON mytable (strength, ...)
... ON mytable (intelligence, ...)      
... ON mytable (speed, ...)      

假设有一个以id作为前导列的合适索引，例如，如果是InnoDB，则id列是主键(集群索引)。使用EXPLAIN查看执行计划。