mysql - MySQL各类前k名学生

Question

我有两个表，如下所示:

Class  Name  Score  Top
1      Amy    90     X
1      Ben    70     X
1      Chu    80     X
2      Don    60     X
2      Elf    65     X
2      Fez    75     X
2      Ges    35     X
2      Han    40     X

Class NumToppers
1      2
2      3

我想从每个类(class)中找到 Top "NumToppers"并相应地更新 "Top"字段:

Class  Name  Score  Top
1      Amy    90     Y
1      Ben    70     N
1      Chu    80     Y
2      Don    60     Y
2      Elf    65     Y
2      Fez    75     Y
2      Ges    35     N
2      Han    40     N

我的真实数据中有 100 个“类”。因此，尽管数据看起来很玩具，但请不要使用玩具解决方案。

Answer 1

引用this excellent page on selecting the 'greatest-n-per-group' ，这是我想出的查询。回想起来，它与@BassamMehanni 的回答基本相同，只是没有 ROW_NUMBER()。 MySQL 中的函数。

假设您有表 class和 toppers .

解决方案:

注意:如果您在 class 上有主 ID不是化合物的表 (Class,Name,Score)组合，将其用于标记为 #@@ 的连接条件.

set @class='';
set @rank=1;
UPDATE class         
LEFT JOIN 
  (SELECT Class,Name,Score,
       @rank:=if(@class=Class,@rank+1,1) as rank, 
       @class:=Class as dummy 
  FROM class ORDER BY Class,Score DESC) c
ON c.Class=class.Class AND c.Score=class.Score  #@@
   AND c.Name=class.Name                        #@@
LEFT JOIN toppers
ON c.Class=toppers.Class
SET Top = (CASE WHEN rank <= NumToppers THEN 'Y' ELSE 'N' END); 

解释

基本上，这个查询:

1. 为 class 的行编号在每个类(class)中，按分数从上到下。也就是说，对每个类(class)的每个学生进行排名。
2. 选择 class 的行为此 rank是<= NumToppers , 对于每个类(class)。
3. 更新这些。

对于第 1 步，请参阅以下内容(来 self 推荐给您的链接):

set @class='';
set @rank=1;
SELECT Class,Name,Score, 
       @num:=if(@class=Class,@rank+1,1) as rank, 
       @class:=Class as dummy 
FROM class ORDER BY Class,Score DESC;

这会遍历 class 的每一行(在按类别和降序排序之后)并设置 rank如果我们要上新课则为 1，或者为 rank+1如果我们在同一个类(class)。

对于第 2 步，我们使用 toppers 进行 JOIN在 class并选择顶部 NumToppers每个类的行数:

set @class='';
set @rank=1;
SELECT *                                          # NEW
FROM toppers                                      # NEW
LEFT JOIN                                         # NEW
  (SELECT Class,Name,Score,                       #\
       @rank:=if(@class=Class,@rank+1,1) as rank, # |(same as step 1)
       @class:=class as dummy                     # |
  FROM class ORDER BY Class,Score DESC) c         #/ 
ON c.Class=toppers.Class                          # NEW
WHERE rank <= NumToppers;                         # NEW

最后，我们更新这些条件(第 3 步)。但是我们必须做 UPDATE class明确地，所以我们必须添加一个额外的 JOIN第 2 步的 class :

set @class='';
set @rank=1;
UPDATE class                                      # NEW
LEFT JOIN 
  (SELECT Class,Name,Score,
       @rank:=if(@class=Class,@rank+1,1) as rank, 
       @class:=class as dummy 
  FROM class ORDER BY Class,Score DESC) c
ON c.Class=class.Class AND c.Score=class.Score    # NEW (join condition)
   AND c.Name=class.Name                          # NEW (join condition)
LEFT JOIN toppers
ON c.Class=toppers.Class
SET top = (CASE WHEN rank <= NumToppers THEN 'Y' ELSE 'N' END); # NEW

如果您在 class 上有主 ID这最好不是复合的(即单列 ID)，然后加入它。