本文总结几种常见的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