mysql：update select groupby orde

前言：

有时候我们需要更新table中分组排序后的第一条数据。比如：给各学科分数第一名的学生打标。

学生各科成绩

如上图所示，现在有一张学生成绩表，我想要各科第一名的学生打标，也就是向mark字段中写入“第一名”标记。那么预想得到的效果应该是这样的：

image.png

结论（相关SQL）：

• mysql5.8以下版本写法：

由于需要使用到group by , 所以需要先检查下mysql对应的sql_mode 是否包含ONLY_FULL_GROUP_BY，如果包含，则将sql_mode中的ONLY_FULL_GROUP_BY移除掉，否则下面的SQL可能执行不成功

UPDATE test 
SET mark='第一名'
WHERE id IN (
    SELECT t2.id FROM (
        SELECT t1.id FROM (
            SELECT DISTINCT(t0.id), t0.`name`, t0.`subject`, t0.score FROM test AS t0 ORDER BY t0.score DESC) AS t1 GROUP BY t1.`subject`
    ) as t2
)

上面sql中t0表为什么需要使用到DISTINCT，可以查看文章：

mysql分组排序，取每组第一条数据

执行结果：

image.png

SQL分析：

image.png

上面的SQL感觉还是过于复杂了，看过下面的文章后：

mysql update select 从查询结果中更新数据
MySQL update 嵌套 select

可以将上面的SQL优化优化.....

优化版写法：

UPDATE test AS t 
INNER JOIN (
    SELECT * FROM (SELECT DISTINCT(t0.id), t0.`name`, t0.`subject`, t0.score FROM test AS t0 ORDER BY t0.score DESC) AS t1 
    GROUP BY t1.`subject`
) as t2
ON t.id = t2.id
SET mark='第一名'

执行结果：

image.png

SQL分析：

image.png

• mysql5.8以上版本写法：

UPDATE test AS t
INNER JOIN (
    SELECT * FROM (
        SELECT t0.id, t0.`name`, t0.`subject`, t0.score, row_number() over (PARTITION BY t0.`subject` ORDER BY t0.score DESC) AS row_num FROM test as t0
    ) as t1 WHERE t1.row_num=1
) as t2
ON t.id = t2.id
SET mark='第一名'

执行结果：

image.png

SQL分析：

image.png

上面的SQL中使用到了窗口函数，关于窗口函数相关文章：

通俗易懂的学会：SQL窗口函数
mysql 分组后每组取第一个_MySql之高级功能
mysql 官方文档

---

DDL&DML

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  `score` int(4) DEFAULT NULL,
  `mark` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of test
-- ----------------------------
BEGIN;
INSERT INTO `test` VALUES (1, '张三', '数学', 89, NULL);
INSERT INTO `test` VALUES (2, '李四', '数学', 77, NULL);
INSERT INTO `test` VALUES (3, '小明', '数学', 81, NULL);
INSERT INTO `test` VALUES (4, '张三', '英语', 66, NULL);
INSERT INTO `test` VALUES (5, '李四', '英语', 97, NULL);
INSERT INTO `test` VALUES (6, '小明', '英语', 73, NULL);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;