mysql 分组后top N_mysql 分组topn limit-CSDN博客

本文链接：https://blog.csdn.net/feifeichongtian/article/details/89874105

直接上代码

建表语句：

CREATE TABLE `studentgrade` (
  `stuId` varchar(255) DEFAULT NULL,
  `subId` int(11) DEFAULT NULL,
  `grade` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('001', '1', '97');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('001', '2', '50');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('001', '3', '70');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('002', '1', '92');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('002', '2', '80');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('002', '3', '30');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('003', '1', '93');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('003', '2', '95');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('003', '3', '85');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('004', '1', '73');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('004', '2', '78');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('004', '3', '87');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('003', '3', '86');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('003', '3', '86');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('004', '3', '87');
INSERT INTO `yz_db`.`studentgrade` (`stuId`, `subId`, `grade`) VALUES ('004', '3', '87');

sql：

SET @row_number1 = 0;
SET @row_number2 = 0;
SELECT
	a.num,
	a.acount,
	a.reporter,
	a.itemTime
FROM
	(
		SELECT
			(
				@row_number1 :=@row_number1 + 1
			) AS 'num',
			mm.acount,
			mm.reporter,
			mm.itemTime
		FROM
			(
				SELECT
					DATE_FORMAT(w.commit_time, '%Y-%m-%d') AS itemTime,
					a.create_by reporter,
					COUNT(a.id) acount
				FROM
					t_workreport w
				INNER JOIN t_workreport_attribute a ON w.id = a.report_id
				WHERE
					w.project_id = 1
				AND w.report_targeter = 12
				AND a.item_id = 25
				GROUP BY
					itemTime,
					reporter
				ORDER BY
					commit_time
			) mm
	) a
WHERE
	(
		SELECT
			count(1)
		FROM
			(
				SELECT
					(
						@row_number2 :=@row_number2 + 1
					) AS 'num',
					ww.acount,
					ww.reporter,
					ww.itemTime
				FROM
					(
						SELECT
							DATE_FORMAT(w.commit_time, '%Y-%m-%d') AS itemTime,
							a.create_by reporter,
							COUNT(a.id) acount
						FROM
							t_workreport w
						INNER JOIN t_workreport_attribute a ON w.id = a.report_id
						WHERE
							w.project_id = 1
						AND w.report_targeter = 12
						AND a.item_id = 25
						GROUP BY
							itemTime,
							reporter
						ORDER BY
							commit_time
					) ww
			) b
		WHERE
			b.itemTime = a.itemTime
		AND b.num > a.num
	) < 10
ORDER BY
	a.itemTime,
	a.reporter DESC