SQL选择组的第n个成员 [英] SQL select nth member of group

问题描述

如果我有一个像

的用户表，那么

  class |年龄
 -------------- 
 1 20 
 3 56 
 2 11 
 1 12 
 2 20 
  

然后，我可以通过 $ b轻松获得每个班级中最年轻的用户$ b

 从用户
中选择class，min（age）
按类别分组; 
  

同样，用max代替min，我可以得到最老的。但是，我怎样才能获得每个班级中的第10个年龄最小（或最早）的 ？顺便说一句，我使用的是MySql v.5.0。

干杯，

解决方案选择a.class，
（
SELECT b.age
FROM用户b
WHERE b.class = a。 class
ORDER BY年龄
LIMIT 1,1
）as age
FROM users a
GROUP BY a.class

每个班级都会获得第二年龄最小的成绩。如果你想要第10个最小的，你会做 LIMIT 9,1 ，如果你想要第10个最老的，你会做 ORDER BY age DESC 。

If I have a USER table like

class | age
--------------
1       20    
3       56
2       11
1       12
2       20

Then I can easily get the youngest user in each class via

select class, min(age)
from   user
group by class;

Similarly, by replacing min with max, I can get the oldest. But how can I get the 10th youngest (or oldest) in each class? By the way, I'm using MySql v.5.0.

Cheers,

解决方案

SELECT a.class,
(
    SELECT b.age 
    FROM users b 
    WHERE b.class = a.class
    ORDER BY age 
    LIMIT 1,1
) as age
FROM users a
GROUP BY a.class

Would get the 2nd youngest in each class. If you wanted the 10th youngest, you'd do LIMIT 9,1 and if you wanted the 10th oldest, you'd do ORDER BY age DESC.

这篇关于SQL选择组的第n个成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！