查询结果中的行号 [英] Row number in query result

问题描述

我想查询按公司去年的销售额来获得的信息.

I have query to get firms by theirs sales last year.

select
   Name,
   Sale
from Sales
order by
   Sale DESC

我明白了

Firm 2 | 200 000
Firm 1 | 190 000
Firm 3 | 100 000

我想获得结果的行索引.对于Firm 2，我想获取0(或1)，对于Firm 3，1(或2)，等等.这可能吗?或至少创建某种自动增量列.如果需要，我甚至可以使用存储过程.

And I would like to get index of row in result. For Firm 2 I would like to get 0 (or 1), for Firm 3 1 (or 2) and etc. Is this possible? Or at least create some sort of autoincrement column. I can use even stored procedure if it is needed.

推荐答案

Firebird 3.0支持row_number()，这是更好的方法.

Firebird 3.0 supports row_number() which is the better way to do this.

但是对于Firebird 2.5，您可以通过相关的子查询获得所需的内容:

However for Firebird 2.5, you can get what you want with a correlated subquery:

select s.Name, s.Sale,
       (select count(*) from Sales s2 where s2.sale >= s.sale) as seqnum
from Sales s
order by s.Sale DESC;

这篇关于查询结果中的行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！