mariadb 获取最高分数并存储在另一列中

6yt4nkrj  于 2022-11-08  发布在  其他
关注(0)|答案(2)|浏览(148)

这是我的表:
| 学生标识|主题标识|总计|最大值|
| - -|- -|- -|- -|
| 一百零一|一个|九十||
| 一百零二|2个|八十个||
| 一百零三|一个|九十五个||
| 一百零一|一个|八十二人||
| 一百零三|2个|七十六人||
| 一百零四|一个|九十五个||
| 一百零一|2个|七十一人||
我想获取特定主题中的最大总计,并在插入或更新总计列时将其存储在另一列中。
这就是我想要的表的外观:
| 学生标识|主题标识|总计|最大值|
| - -|- -|- -|- -|
| 一百零一|一个|九十|九十五个|
| 一百零二|2个|八十个|八十个|
| 一百零三|一个|九十五个|九十五个|
| 一百零一|一个|八十二人|九十五个|
| 一百零三|2个|七十六人|八十个|
| 一百零四|一个|九十五个|九十五个|
| 一百零一|2个|七十一人|八十个|
我尝试过这个查询,但它没有将所有student_id的每个科目的最高分数相加。

SELECT MAX(`total`) AS highest 
FROM results 
GROUP BY student_id

我想我应该为此使用一个触发器,但是普通的查询对我来说也是可以的。

txu3uszq

txu3uszq1#

如果您的数据行尚未存在于数据表中,则需要使用ALTER陈述式来加入它。

ALTER TABLE <your_table_name> ADD max INT;

然后,您可以首先使用聚合为每个“subject_id”选择“max”值,然后在UPDATE语句中使用它(利用JOIN操作):

UPDATE     <your_table_name>
INNER JOIN (SELECT subject_id, 
                   MAX(total) AS total_max
            FROM <your_table_name>
            GROUP BY subject_id) cte
        ON <your_table_name>.subject_id = cte.subject_id
SET <your_table_name>.max = cte.total_max;

检查演示here

y1aodyip

y1aodyip2#

假设您使用的是MySQL 8+。
理想情况下,您不应该将MaxTotal数据存储到每个列中,而应该在进行如下选择时获取这些数据:

WITH CTE AS (SELECT subject_id,MAX(total) AS MaxTotal
FROM results
GROUP BY subject_id
)
SELECT results.*,CTE.MaxTotal
FROM results
JOIN CTE ON results.subject_id = CTE.subject_id;

但是,如果仍然需要更新它,请将UPDATEJOIN一起使用

WITH CTE AS (SELECT subject_id,MAX(total) AS MaxTotal
FROM results
GROUP BY subject_id
)
UPDATE results 
JOIN CTE ON results.subject_id = CTE.subject_id
SET results.MaxTotal = CTE.MaxTotal;

更新后的输出:
| 学生标识|主题标识|总计|最大总计|
| - -|- -|- -|- -|
| 一百零一|一个|九十|九十五个|
| 一百零二|2个|八十个|八十个|
| 一百零三|一个|九十五个|九十五个|
| 一百零一|一个|八十二人|九十五个|
| 一百零三|2个|七十六人|八十个|
| 一百零四|一个|九十五个|九十五个|
| 一百零一|2个|七十一人|八十个|
见此db<>fiddle

相关问题