SQL选择第n个组成员

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.

同样的，用max代替min，我可以得到最大的。但是我怎样才能在每节课上获得第10个最小的(或最老的)呢?顺便说一下，我用的是MySql v5.0。

Cheers,

欢呼,

6 个解决方案

#1

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.

在每个班级中都是第二年轻的。如果你想要第10个最小的，你会限制9 1，如果你想要第10个最大的，你会按年龄顺序订购。

#2

Here N presents Nth record oldest

这是第N项记录中最古老的

SELECT *
FROM users k
WHERE N = (SELECT
             COUNT( DISTINCT age)
           FROM users u
           WHERE k.age >= u.age
               AND k.class = u.class
           GROUP BY u.class)

and it gives Nth record youngest

它给出了第n项最年轻的记录

SELECT *
FROM users k
WHERE N = (SELECT
             COUNT(DISTINCT age)
           FROM users u
           WHERE k.age <= u.age
               AND k.class = u.class
           GROUP BY u.class)

#3

The only sql independent way (even if you don't have subqueries mysql <5)

唯一独立于sql的方法(即使您没有子查询mysql <5)

 select  u1.class, u1.age, count(*)  from      user u1 join user u2 
 on u1.class = u2.class and u1.age >= u2.age
 group by u1.class, u1.age
 having count(*) = [number]

gets you the [number] oldest per class

为您获取每个类的[号码]最老的

 select  u1.class, u1.age, count(*)  from      user u1 join user u2 
 on u1.class = u2.class and u1.age <= u2.age
 group by u1.class, u1.age
 having count(*) = [number]

gets you the [number] youngest per class

让你成为班级中最年轻的

If two people have the same age it may not work as both are returned. If you want to only return one of them you will need a unique key and the query is more complicated.

如果两个人有相同的年龄，这可能不会起作用，因为两者都返回。如果您只想返回其中的一个，您将需要一个唯一的键，而查询更加复杂。

#4

It's pretty easy in SQL Server:

在SQL Server中非常简单:

select 
  *
from(
   select 
      *,
      row_number() over(order by age asc) as eldest
   from class order by age asc) a
where a.eldest = 10

Following that pattern, for MySQL, I think you'd want to look at this: http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/

按照这个模式，对于MySQL，我认为您应该看看这个:http://www.xaprb.com/blog/2006/12/02/how-to- row -in mysql/

#5

Any answer that joins a table on it self will create a square law...

任何连接表的答案都将创建一个平方定律……

- a JOIN b ON a.class = b.class AND a.age >= b.age  
- on average the >= condition will be true for half the class  

- 6 people in a class  
->6*6/2 = 18

- 10 people in a class
->10*10/2 = 50

-> very rapid growth

As the table sizes grow the performance will degrade rapidly. If you're keeping things small and they won't grow much, is it an issue? Your call there...

随着表大小的增长，性能会迅速下降。如果你把事情做得很小，而它们不会增长很多，这是一个问题吗?你的电话有…

An alternative involves more code, but grows linearly...

另一种方法涉及更多的代码，但是线性增长……

First, insert all the records intoa new table, with an IDENTITY field, ordered by Class then Age
首先，将所有记录插入到一个新表中，该表具有标识字段，按类和年龄排序
Now, for each class, find the MIN(id)
现在，对于每个类，找到最小值(id)
Now, for each class, rinf the record where is = MIN(id) + 8 (for the 9th eldest)
现在，对于每个类，记录为= MIN(id) + 8(第9大)

There are a lot of ways of doing the last 2 steps. I personally would use...

完成最后两个步骤有很多方法。我个人会用……

SELECT
    [USER_WITH_IDS].id,
    [USER_WITH_IDS].class,
    [USER_WITH_IDS].age
FROM
    [USER_WITH_IDS]
WHERE
    [USER_WITH_IDS].id = (
                          SELECT
                              MIN([min].ID) + 8
                          FROM
                              [USER_WITH_IDS] AS [min]
                          WHERE
                              [min].class = [USER_WITH_IDS].class
                         )

What this gives is...

这给了…

One pass to create the new IDs
一个通过创建新id。
One pass to get the MIN(id) for each class
获得每个类的最小值(id)的一次通行证
One pass to get the records you need

一个通行证来获得你需要的记录。
And depending on how good the optimiser is, using an index (class then id) would allow it to combine the last 2 passes into 1 pass.

根据优化器的性能，使用索引(类然后id)可以将最后的2次传递合并为1次传递。

2 or 3 passes, no matter how big the table or class size gets. Linear, not square law...

2或3次传递，无论表或类的大小有多大。线性的,不是平方律……

#6

 SELECT 
     userid,  
     class,  
     age,  
     (    SELECT COUNT(1) FROM user  
          WHERE class = c.class  AND age > u.age  
     ) AS oldercount  
FROM user AS u  
WHERE oldercount = 9
GROUP BY class

或

 SELECT userid,  
         class,  
         age  
  FROM user AS u    
  WHERE (SELECT COUNT(1) FROM class WHERE class = c.class AND age > u.age) = 9  
GROUP BY class

#1

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