SQL 分组、求和、排名

MySQL 数据根据分组求和在进行排名

一、先熟悉MySQL根SQL Server或Orcal的区别，MySQL是不存在rank()函数

1. 声明一个变量，用@来进行声明。例如：@rank
2. 给变量赋值，sql中用 ‘:=’ 给变量赋值。例如：@rank := 0

二、创建SQL语句

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
  `st_number` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生编号',
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `consume` decimal(9, 2) NULL DEFAULT NULL COMMENT '花费',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '000120', '张三', 11.23, '2020-09-29 15:40:02');
INSERT INTO `student` VALUES (2, '000121', '李四', 10.50, '2020-09-29 15:41:21');
INSERT INTO `student` VALUES (3, '000123', '王五', 13.80, '2020-09-29 15:41:52');
INSERT INTO `student` VALUES (4, '000120', '张三', 8.26, '2020-09-29 15:42:17');
INSERT INTO `student` VALUES (5, '000121', '李四', 6.50, '2020-09-29 15:43:02');

SET FOREIGN_KEY_CHECKS = 1;

创建后的表结构

三、首先对数据进行分组，求和

MySql5.7以前版本 是可以查分组的

SELECT *,SUM(consume) as sume FROM student GROUP BY st_number

查询结果

MySql8 必须要有聚合函数 执行以下sql可查询

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

四、关键一步，对数据进行排名

这是MySQL 8.0以前的写法

SELECT
	a.*,
	@rank := @rank + 1 AS rank 
FROM
	( SELECT s.*, SUM( consume ) AS sume FROM student AS s GROUP BY s.st_number ORDER BY sume DESC ) a,(
	SELECT
	@rank := 0 
	) r

这是MySQL 8.0的写法 当然上面的SQL也能用 但是 rank 在MySQL 8中是关键词 要用别的代替

SELECT
	a.*,
	dense_rank() over ( ORDER BY a.sume DESC ) ranks 
FROM
	( SELECT s.*, SUM( consume ) AS sume FROM student AS s GROUP BY s.st_number ) a