生成行号而不对任何列进行排序

Question

我希望生成行号的顺序与添加数据的顺序相同。下面的查询在SQL Server上运行正常。

SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO FROM TestTable

我需要标准查询才能在Firebird中实现相同的场景。关于这一点，有人能给我建议吗？

Answer 1

不能将row_number() over (order by (select 100))与火鸟一起使用，因为火鸟--正如SQL标准所要求的--需要为select提供一个from子句。火鸟中的等价物是row_number() over (order by (select 100 from rdb$database))。

最好的解决方案是为order by使用实际的列，以确保确定的顺序。

查看SQL:2016标准时，row_number()不需要order by (但rank()和dense_rank()需要)。不幸的是，微软似乎也对row_number()应用了这一要求，可能是为了与排名函数保持一致，也可能是因为没有顺序的row_number()没有多大意义。在SQL Server中使用Firebird会产生错误“函数'row_number‘必须具有OVER子句with ORDER BY。”，但它可以与Firebird一起使用。

SQL Server还强制要求窗口函数中的order by不是数值列引用。将row_number() over (order by 1)与SQL Server一起使用会产生错误"Windowed functions，aggregates和NEXT VALUE FOR functions不支持整数索引作为ORDER BY子句表达式。“，但可以与Firebird一起使用(尽管1被视为文字1，而不是列引用，这与select级别上的order by相反)。

SQL Server也不支持在窗口函数的order by中使用常量或文字。将row_number() over (order by '1')与SQL Server一起使用会产生错误“窗口函数、聚合和函数的NEXT VALUE不支持常量作为ORDER BY子句表达式。”，但适用于Firebird。

我确实发现了一个在Firebird 3和SQL Server 2017上都有效的技巧，但它是一个肮脏的黑客：

row_number() over (order by current_user)

这之所以有效，是因为SQL Server不将current_user视为常量，而是将其视为函数，这意味着它不属于“不允许常量”的规则。

请注意，这种技巧可能会产生不一致的行数(例如，在Firebird中，使用不同常量计算的多个窗口函数将产生不同的值，并且窗口函数在select级别上的order by之前计算)，您可能想要考虑是否不应该简单地跟踪应用程序中的行索引。

Answer 2

一种方法是使用ORDER BY RAND()

CREATE TABLE TestTable(i INT);
INSERT INTO TestTable(i) VALUES (10);
INSERT INTO TestTable(i) VALUES (20);
INSERT INTO TestTable(i) VALUES (30);

SELECT TestTable.*,ROW_NUMBER() OVER (ORDER BY RAND()) AS SNO 
FROM TestTable;

db<>fiddle demo - Firebird

db<>fiddle demo - SQL Server