MySQL中是否有像Oracle这样的功能，如density_rank()和row_number()? [英] Are there any functions in MySQL like dense_rank() and row_number() like Oracle?

问题描述

MySQL中是否有像Oracle和其他DBMS提供的功能的dense_rank()和row_number()之类的功能?

Are there any functions in MySQL like dense_rank() and row_number() like those provided by Oracle and other DBMS?

我想在查询中生成一个id，但是在MySQL中，这些函数不存在.有其他选择吗?

I want to generate an id within the query, but in MySQL these functions are not there. Is there an alternative?

推荐答案

MySQL没有它们，但是您可以使用使用用户定义变量的以下表达式来模拟row_number():

Mysql doesn't have them, but you can simulate row_number() with the following expression that uses a user defined variable:

(@row := ifnull(@row, 0) + 1)

像这样:

select *, (@row := ifnull(@row, 0) + 1) row_number
from mytable
order by id

，但是如果您要重新使用会话，则仍会设置@row，因此您需要像这样重置它:

but if you're reusing the session, @row will still be set, so you'll need to reset it like this instead:

set @row := 0;
select *, (@row := @row + 1) row_number
from mytable
order by 1;

请参见 SQLFiddle .

dense_rank()是可能的，但火车残骸；我建议在应用程序层中处理该要求.

dense_rank() is possible but a train wreck; I advise handling that requirement in the app layer.

这篇关于MySQL中是否有像Oracle这样的功能，如density_rank()和row_number()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！