案例一

在开发过程中，会遇到分组后取每一组排名前几的信息，具体实现的sql如下：

SELECT
	a.id,
	a.NAME '姓名',
	a.achievement '成绩',
	a.category '类别' 
FROM
	`user` a;

 

需求:按category分组，查出achievement最高的每组的前三名

SELECT
	a.id,
	a.NAME '姓名',
	a.achievement '成绩',
	a.category '类别' 
FROM
	`user` a 
WHERE
	3 > ( 
	SELECT COUNT(*) FROM `user` b 
	WHERE b.category = a.category 
	AND b.achievement > a.achievement 
	) 
ORDER BY
	a.category,
	a.achievement DESC

sql解析：

• 3>:目的是获取前三条数据。
• where b.category=a.category :目的是确定分组字段
• b.achievement > a.achievement 排序字段 

案例二 

需求： 将数据按照用户进行分组，并根据城市和amount进行排序，取出每个城市排名前3的用户数据（Mysql5.7）

测试数据文件：test.sql

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `user_id` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `amount` float(255,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('21e656ef-395d-4f9b-993a-dcc0e1a2db1a', '北京', '女', '4800');
INSERT INTO `test` VALUES ('2d165f95-43a4-402a-9c99-7817d583a199', '广州', '女', '23272');
INSERT INTO `test` VALUES ('0a274d08-453f-4cc3-9380-3fe74f6b7318', '北京', '女', '155480');
INSERT INTO `test` VALUES ('171f6549-9f88-4747-8a4a-33dcf1cb6527', '广州', '男', '80000');
INSERT INTO `test` VALUES ('127b017e-5af9-46dd-a15b-2974faa51d98', '上海', '女', '96000');
INSERT INTO `test` VALUES ('22be3b12-2e9d-46b8-93ab-ed3df13dcb74', '北京', '女', '36000');
INSERT INTO `test` VALUES ('266bfcd7-3ea9-4817-baf8-2ba96293b7d8', '广州', '男', '24000');
INSERT INTO `test` VALUES ('16c02230-7d5e-46f1-9ba5-cb0e34122395', '北京', '女', '320000');
INSERT INTO `test` VALUES ('18bb0ba2-966a-486e-81c5-f7821982a2ff', '上海', '男', '17600');
INSERT INTO `test` VALUES ('293424f8-a8cd-42eb-8b5a-7bc85fc25b9f', '北京', '女', '16838');
INSERT INTO `test` VALUES ('00b44351-ed41-4d5e-815f-a5319aa44575', '上海', '女', '160000');

 实现结果：

SQL查询语句

SET @rownum := 0 ,@GROUP := ''; //初始化变量

SELECT
	user_id,
	sex,
	city,
	amount,
	r AS rownum
FROM
	(
	 SELECT
			@rownum := IF (@GROUP = city ,@rownum + 1, 1) AS r,
		  	@GROUP := city AS g,
			t1.*
	 FROM
		 (
			SELECT
				user_id,
				sex,
				city,
				amount
			FROM
				test
			GROUP BY
				user_id
			ORDER BY
				city,
				amount DESC
		) t1
	) t2
WHERE
	r < 4;

MySQL8

公共表达式实现分组取前N位

WITH table_name AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY 区分字段 ORDER BY 排序依据 DESC) AS rn
    FROM
        表名
)
SELECT * FROM table_name 
WHERE rn <= 3;

注意; 如果语句中有group by子句，那么select后面就必须是group by后面的属性或者是 某聚集函数（group by后面没有的属性）。这个规则是由配置sql_mode的ONLY_FULL_GROUP_BY决定的。
案例二的sql明显违反了这个规定，执行select @@sql_mode，发现Mysql5.7没有ONLY_FULL_GROUP_BY选项，故没这个限制。
把sql移到mysql8执行，报错 XXXXXXXX which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by,执行select @@sql_mode，mysql8配置了ONLY_FULL_GROUP_BY选项。

 原文：

mysql分组排序后，取前三

mysql实现分组取每组前几