Oracle:使用"order by"子句显示行号 [英] Oracle: Display row number with 'order by' clause

问题描述

我想知道如何在使用order的情况下为sql语句打印行号. 目前，我尝试了ROWNUM，但据我了解，它仅适用于未排序的结果集.

I wonder how could i print a row number for sql statement where is using order. Currently i tried ROWNUM but as i understand it works only for unsorted result set.

SELECT rownum, a.lg_id, a.full_name, a.sort_order
  FROM activity_type_lang a
  where a.lg_id = 'en'
  order by a.full_name;

TIA

推荐答案

除了嵌套查询之外，您还可以使用解析函数

In addition to nesting the query, you can use an analytic function

SELECT row_number() OVER (ORDER BY a.full_name),
       lg_id,
       full_name,
       sort_order
  FROM activity_type_lang a
 WHERE a.lg_id = 'en'
 ORDER BY a.full_name

如果想更改联系的处理方式，则使用分析功能也使操作变得更加容易.您可以将ROW_NUMBER替换为RANK或DENSE_RANK.

Using analytic functions also makes it easier if you want to change how ties are handled. You can replace ROW_NUMBER with RANK or DENSE_RANK.

这篇关于Oracle:使用"order by"子句显示行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！