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

我想从每个类中找到排名靠前的“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

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

Answer 1

参考 这个关于选择“每个组中最伟大的n个”的优秀页面，这是我提出的查询。回想起来，它与 @BassamMehanni 的答案基本相同，只是 MySQL 中没有 ROW_NUMBER() 函数。

这假设您有表 class 和 toppers。

解决方案：

注意：如果 class 表上的主 ID 不是复合 (Class,Name,Score) 组合，请使用该主 ID 作为连接条件标记为#@@。

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 的行进行编号。也就是说，对每个班级中的每个学生进行排名。
2. 对于每个类，选择 rank 为 <= NumToppers 的 class 行。
3. 更新这些。

对于第 1 步，请参阅以下内容（来自我向您推荐的链接）：

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。

对于第 2 步，我们在 class 上使用 toppers 进行 JOIN，并为每个类选取顶部的 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，因此我们必须在步骤 2 中使用 class 添加额外的 JOIN：

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

如果您有主 ID在最好不是复合的类（即一列 ID）上，然后加入它。