如何用临时表的结果更新MySQL表

Question

我有一张名为assp1的桌子，上面有字段FirstName、LastName、Overall_Ranking和数学。我正在运行一个查询，该查询将根据数学成绩对每个学生进行排名。查询工作正常，但它有一个临时变量和一个临时表。因此，它只显示查询的结果，但不影响表。我想要的是将查询结果放到Overall_Ranking字段中。这样，按降序排列的排名将显示谁排在第一位至最后。下面是代码，请展示如何将结果放入assp1表的assp1列中。

SELECT assp1.FirstName, assp1.Maths, assp1.LastName, @prev := @curr , @curr := Total_Score, @rank := IF( @prev = @curr , @rank , @rank +1 ) AS Overall_Ranking
FROM assp1, (
SELECT @curr := NULL , @prev := NULL , @rank :=0
)tmp_tbl
WHERE assp1.Grade = 'Grade7' && assp1.class = '7A' && YEAR( assp1.created_at ) = YEAR( CURDATE( ) )
ORDER BY assp1.Total_Score DESC

Answer 1

基本上你加入了原始和你的选择

我简化了你的查询

UPDATE assp1 a INNER JOIN (SELECT assp1.FirstName, assp1.Maths, assp1.LastName, @rank := IF( @curr = Total_Score , @rank , @rank +1 ) AS Overall_Ranking, @curr := Total_Score as Total_Score
FROM assp1, (
SELECT @curr := NULL , @prev := NULL , @rank :=0
)tmp_tbl
WHERE assp1.Grade = 'Grade7' && assp1.class = '7A' && YEAR( assp1.created_at ) = YEAR( CURDATE( ) )
ORDER BY assp1.Total_Score DESC) b ON a.FirstName = b.FirstName AND a.Maths = b.Maths  AND a.LastName = b.LastName 
SET a.Overall_Ranking = b.Overall_Ranking

Answer 2

我强烈地、强烈地鼓励你使用窗口功能：

UPDATE assp1 a JOIN
       (SELECT a.*,
               RANK() OVER (PARTITION BY a.Total_Score DESC) as seqnum
        FROM assp1 a
        WHERE a.Grade = 'Grade7' AND A.class = '7A' AND
              YEAR( assp1.created_at ) = YEAR( CURDATE( ) )
       ) a2
       ON a.FirstName = a2.FirstName AND a.LastName = a2.LastName
    SET a.overall_ranking = a2.seqnum;

请注意，这假设学生名称是唯一的，以标识每一行。

我为什么要推荐这个？首先，在非SET语句中设置变量在MySQL中是不可取的，从MySQL 8.0开始。所以，变量并不是正确的选择。

其次，如果要在早期版本的MySQL中使用变量，则必须非常小心: MySQL不能保证表达式的计算顺序。因此，您不能在一个表达式中设置一个变量，而在另一个表达式中安全地使用它。