SQL 序号 排序

一，使用窗口函数（mysql中不可用），row_number() /rank() /dense_rank()
1.row_number(): 重复值序号不相同

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

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

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

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

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

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

SELECT a.*, ( SELECT count(DISTINCT amount) FROM deal WHERE amount >= a.amount ) AS rank FROM deal a ORDER BY rank

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

SET @no := 0; SELECT *,@no := @no + 1 AS no FROM deal ORDER BY amount DESC