在Mysql中模拟ROW_NUMBER, RANK, DENSE_RANK

从MariaDB 10.2.0版本,开始支持window类函数,但现在还不是正式版本,不建议在生产环境使用。
可以使用下面的sql语句,模拟ROW_NUMBER, RANK, DENSE_RANK函数的功能。这种写法不需要做表的自连接,性能会比较好。

SELECT
  @row_num:=IF(@prev_col1=t.col1 AND @prev_col2=t.col2, @row_num+1, 1) AS row_number,
  @dense:=IF(@prev_col1=t.col1 AND @prev_col2=t.col2, IF(@prev_col3=col3, @dense, @dense+1), 1) AS dense_rank,
  @rank:=IF(@prev_col1=t.col1 AND @prev_col2=t.col2 AND @prev_col3=col3, @rank, @row_num) AS rank,
  t.*,
  @prev_col1:=t.col1, @prev_col2:=t.col2, @prev_col3:=t.col3
FROM (SELECT * FROM table1 ORDER BY col1, col2, col3 DESC) t,
     (SELECT @row_num:=1, @dense:=1, @rank:=1, @prev_col1:=NULL, @prev_col2:=NULL, @prev_col3:=NULL) var

Comments are closed.