MySQL _ MySQL 中通过变量定义实现 topN 功能：

 

参考文章：

1. MySQL中row_number的实现

https://blog.csdn.net/zhouli2008/article/details/79769607

 

 

       紧接着之前Hive 中 分组实现 topN ,  我们看一下 MySQL 如何实现对应的功能。 MySQL 并没有 row_number() over()  函数，但是我们可以先通过排序 + 变量 的方式实现类似的功能。

 

表结构如下：

  `username` varchar(255) DEFAULT NULL,  
  `subject` varchar(255) DEFAULT NULL,
  `score` double DEFAULT NULL

username 表示用户名

subject 表示所选择的科目

score 表示该科目的得分

 

 

我们创建 Mysql 表，并插入数据

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for topn
-- ----------------------------
DROP TABLE IF EXISTS `topn`;
CREATE TABLE `topn` (
  `username` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `score` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of topn
-- ----------------------------
INSERT INTO `topn` VALUES ('a', 'chinese', '22');
INSERT INTO `topn` VALUES ('a', 'english', '63');
INSERT INTO `topn` VALUES ('a', 'math', '44');
INSERT INTO `topn` VALUES ('b', 'chinese', '33');
INSERT INTO `topn` VALUES ('b', 'english', '54');
INSERT INTO `topn` VALUES ('b', 'math', '47');
INSERT INTO `topn` VALUES ('c', 'chinese', '76');
INSERT INTO `topn` VALUES ('c', 'english', '74');
INSERT INTO `topn` VALUES ('c', 'math', '43');
INSERT INTO `topn` VALUES ('d', 'chinese', '33');
INSERT INTO `topn` VALUES ('d', 'english', '43');
INSERT INTO `topn` VALUES ('d', 'math', '44');

 

 

查询语句如下：

#select * from topn GROUP BY topn.`subject` ORDER BY topn.score;
SELECT
    username AS '原数据ID',
    `subject` AS '分组ID',
    score AS '排序条件',
    num AS '排名'
FROM
    (
        SELECT
            @row_number := CASE
        WHEN @subject_no = `subject` THEN
            @row_number + 1
        ELSE
            1
        END AS num /*如果同group_id那么排名加1,否则更新为1*/
        ,@subject_no := `subject` AS `subject` /*重新加载group_id*/
        ,
        username,
        score /*原有数据*/
    FROM
        topn,
        (
            SELECT
                @subject_no := 0,
                @row_number := 0
        ) AS t
    ORDER BY
        topn.`subject`,
        topn.score DESC,
        username
    ) a
WHERE
    num <= 3

 

#select * from topn GROUP BY topn.`subject` ORDER BY topn.score;
SELECT
	username AS '原数据ID',
	`subject` AS '分组ID',
	score AS '排序条件',
	num AS '排名'
FROM
	(
		SELECT
			@row_number := CASE
		WHEN @subject_no = `subject` THEN
			@row_number + 1
		ELSE
			1
		END AS num /*如果同group_id那么排名加1,否则更新为1*/
		,@subject_no := `subject` AS `subject` /*重新加载group_id*/
		,
		username,
		score /*原有数据*/
	FROM
		topn,
		(
			SELECT
				@subject_no := 0,
				@row_number := 0
		) AS t
	ORDER BY
		topn.`subject`,
		topn.score DESC,
		username
	) a
WHERE
	num <= 3

 

思路如下：

1.  按照 1.科目 2.成绩 对原有成绩排序。

2.  用两个两个变量 分别存储 当前科目 与 科目的排名，如果与之前科目相同，则排名+1 ，否则为1

3.  得到排名后，筛选排名前几的数据。