mysql - 模拟 row_number() 的此 mysql 查询的 MSSQL 等效项

Question

MS-SQL 的 row_number() 可以用下面的查询模拟，但我需要这个查询的 MS-SQL 实现。

 SELECT @row_number:= IF(@column_name = column_name, @row_number+1, 1) AS row_no,
 @column_name:= column_name AS column_name
 FROM db_name, (SELECT @row_number := 0, @column_name := '') AS temp
 ORDER BY column_name;

Answer 1

SELECT *, ROW_NUMBER() OVER (ORDER BY column_name)
FROM db_name

编辑

没有 row_number()

select *, (     
    select count(*)     
    from db_name p2     
    where p1.column_name >= p2.column_name ) 
as cnt from db_name p1

根据 Damien 的评论编辑 2

非 row_number() 版本仅在您的 column_name 列的内容不包含重复项时才有效。否则，row_number 结果列中将出现重复和跳过。