SQL实现排序，并输出序号

本文总结几种常见的SQL排序，并输出序号的方法
数据源：

CREATE TABLE `deal` (
  `usid` varchar(255) DEFAULT NULL,
  `time` varchar(255) DEFAULT NULL,
  `amount` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
INSERT INTO `deal` VALUES ('1', '2019-02-08', '6214');
INSERT INTO `deal` VALUES ('1', '2019-02-08', '8247');
INSERT INTO `deal` VALUES ('1', '2019-02-09', '850');
INSERT INTO `deal` VALUES ('1', '2019-02-10', '985');
INSERT INTO `deal` VALUES ('1', '2019-02-09', '285');
INSERT INTO `deal` VALUES ('1', '2019-02-11', '1285');
INSERT INTO `deal` VALUES ('2', '2019-02-14', '1037');
INSERT INTO `deal` VALUES ('2', '2019-02-15', '369');
INSERT INTO `deal` VALUES ('2', '2019-02-15', '795');
INSERT INTO `deal` VALUES ('2', '2019-02-19', '715');
INSERT INTO `deal` VALUES ('2', '2019-02-21', '537');
INSERT INTO `deal` VALUES ('2', '2019-02-24', '1037');
INSERT INTO `deal` VALUES ('3', '2019-02-09', '967');
INSERT INTO `deal` VALUES ('3', '2019-02-10', '850');
INSERT INTO `deal` VALUES ('3', '2019-02-10', '769');
INSERT INTO `deal` VALUES ('3', '2019-02-13', '940');
INSERT INTO `deal` VALUES ('3', '2019-02-19', '843');
INSERT INTO `deal` VALUES ('3', '2019-02-11', '850');
INSERT INTO `deal` VALUES ('3', '2019-02-14', '843');
INSERT INTO `deal` VALUES ('3', '2019-02-15', '900');

场景一：按照amount字段进行排序，并输出序号
1.使用窗口函数（mysql8以下版本不可用），row_number() /rank() /dense_rank()
（1）row_number(): 重复值序号不相同

SELECT
	usid,
	time,
	amount,
	row_number() over (ORDER BY amount DESC) AS row_number
FROM
	deal
ORDER BY
	row_number

（2）rank(): 重复值序号相同，序号不连续

SELECT
	usid,
	time,
	amount,
	rank () over (ORDER BY amount DESC) AS rank
FROM
	deal
ORDER BY
	rank

（3）dense_rank(): 重复值序号相同，序号连续

SELECT
	usid,
	time,
	amount,
	dense_rank() over (ORDER BY amount DESC) AS dense_rank
FROM
	deal
ORDER BY
	dense_rank

2.不使用窗口函数（如mysql中）

#排序效果和dense_rank()窗口函数一样
SELECT
	a.*, (
		SELECT
			count(DISTINCT amount)
		FROM
			deal
		WHERE
			amount >= a.amount
	) AS rank
FROM
	deal a
ORDER BY
	rank

3.利用变量（MySQL中）
(1) 遇到相同值继续排序，序号连续不重复，排序效果同row_number()窗口函数

SET @rank := 0;

SELECT
	*,@rank := @rank + 1 AS rank
FROM
	deal
ORDER BY
	amount DESC

（2）相同值序号相同，且序号连续，效果同dense_rank()窗口函数

SET @rank := 0, @prev := NULL;

SELECT
	*, CASE
WHEN @prev = amount THEN
	@rank
WHEN @prev := amount THEN  #when后面如果是赋值语句，则when和then语句都会执行
	@rank := @rank + 1
END AS rank
FROM
	deal
ORDER BY
	amount DESC

场景二：对每个用户usid按照amount字段进行排序，并输出序号（即：组内排序并输出序号）
1 窗口函数：以dense_rank()为例，其他方法相同

SELECT
	usid,
	time,
	amount,
	dense_rank () over (
		PARTITION BY usid
		ORDER BY
			amount DESC
	) AS dense_rank
FROM
	deal
ORDER BY
	usid,
	dense_rank

2 不使用窗口函数（如mysql中）

#排序效果同dense_rank()
SELECT
	a.*, (
		SELECT
			count(DISTINCT amount)
		FROM
			deal
		WHERE
			amount >= a.amount
		AND a.usid = usid
	) AS rank
FROM
	deal a
ORDER BY
	usid,
	rank