mysql分组排序
mysql分组排序
·
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
注:自定义的两个变量都要进行声明赋值,否则会出问题,感兴趣的可以自己试一下,并且想想为什么
更多推荐
已为社区贡献1条内容
所有评论(0)