mysql分组排序

最新推荐文章于 2024-07-13 10:38:18 发布

飞不动的飞

最新推荐文章于 2024-07-13 10:38:18 发布

阅读量1.4k

点赞数

分类专栏：数据库 MySql 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/qq_34374058/article/details/124818659

版权

数据库同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

MySql

3 篇文章 0 订阅

订阅专栏

mysql 5.7及以下版本不包含分组函数，只能自定义分组

先期准备：创建表以及插入数据

CREATE TABLE `score`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `score` VALUES (1, '张三', '67', '语文');
INSERT INTO `score` VALUES (2, '张三', '80', '数学');
INSERT INTO `score` VALUES (3, '李四', '90', '语文');
INSERT INTO `score` VALUES (4, '李四', '46', '数学');
INSERT INTO `score` VALUES (5, '王五', '5', '语文');
INSERT INTO `score` VALUES (6, '王五', '0', '数学');
INSERT INTO `score` VALUES (7, '刘二麻子', '99', '语文');
INSERT INTO `score` VALUES (8, '刘二麻子', '45', '数学');

查找要求：查找每科最高分，以及得分人：

select score,name,type
from (select (@row_number1 := CASE WHEN @type = type THEN @row_number1 + 1 ELSE 1 END ) AS rn, score,name,type,  @type := type 
		from ( select score,name,type
				from score r  
					order by type,score desc 
		) a ,
		(SELECT @row_number1:=0, @type :='') AS t 
)t 
where rn = 1

注：自定义的两个变量都要进行声明赋值，否则会出问题，感兴趣的可以自己试一下，并且想想为什么