时间戳

首页 > 解决方案 > 如何使用临时表的结果更新 MySQL 表

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

问题描述

我有一个名为assp1的表，其中包含FirstName、LastName、Maths和Overall_Ranking 字段。我正在运行一个查询，该查询将根据数学分数对每个学生进行排名。该查询工作正常，但它有一个临时表的临时变量。所以它只显示查询的结果而不影响表。我想要的是将查询结果放入Overall_Ranking字段。这样排名就会按降序显示谁排在最后。下面是代码，请说明如何将结果放入assp1表的Overall_Ranking列。

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

标签： mysqlsql

解决方案

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

我简化了你的查询

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

推荐阅读