众所周知,SQL Server对SQL标准有非常严格的解释。 例如,以下表达式或语句在SQL Server中是不可能的:
-- Get arbitrarily numbered row_numbers
SELECT ROW_NUMBER() OVER ()
-- Skip arbitrary rows
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
OFFSET 3 ROWS
严格来说,该限制是有道理的,因为上述ROW_NUMBER()
或OFFSET
表达式是不确定的。 同一查询的两次后续执行可能会产生不同的结果。 但是再说一次,如果您不按严格的UNIQUE
表达式(例如主键)排序,则任何ORDER BY
子句都是不确定的。
因此,这有点麻烦,因为其他数据库的要求不是那么严格,毕竟,您可能不关心快速的即席查询的显式排序,因此“合理”,宽松的默认设置将很有用。
常量ORDER BY子句不起作用
您也不能向窗口函数添加常量ORDER BY
子句。 即:
-- This doesn't work:
SELECT ROW_NUMBER() OVER (ORDER BY 'a')
-- But this does!
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
ORDER BY 'a'
OFFSET 3 ROWS
请注意, ORDER BY 'a'
使用常量VARCHAR
表达式,而不是数字表达式,因为这将生成按索引的列引用表达式,在第二个示例中,该表达式不是恒定的。
随机列引用不起作用
因此,您在想可以只添加一个随机列引用? 有时可以,但通常不能:
-- This doesn't work:
SELECT ROW_NUMBER() OVER (
ORDER BY [no-column-available-here]
)
-- But this does!
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
ORDER BY a
OFFSET 3 ROWS
上面的示例表明,在任何给定的SQL表达式中,并非始终都有可用的列引用。 没有可以从ROW_NUMBER()
函数引用的有用列。 同时,您可以在第二个示例中编写ORDER BY a
,但前提是a
是“可比较”值,即不是LOB,例如text
或image
。
此外,由于我们并不真正在乎实际的排序,因此是否值得对所有结果集进行排序? 你正好有一个指标a
?
准常数ORDER BY表达式可以正常工作
因此,为了安全起见,如果您在SQL Server中需要虚拟的ORDER BY
表达式,请使用准常量表达式,例如@@version
(或@@language
或任何一个 )。 以下内容将始终有效:
-- This doesn't work:
SELECT ROW_NUMBER() OVER (ORDER BY @@version)
-- But this does!
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
ORDER BY @@version
OFFSET 3 ROWS
从即将发布的jOOQ 3.4中 ,我们还将生成这种综合的ORDER BY
子句,这些子句将帮助您简化在这些极端情况下编写与供应商无关的SQL,因为我们认为您根本不应该一直考虑这些事情。
翻译自: https://www.javacodegeeks.com/2014/05/sql-server-trick-circumvent-missing-order-by-clause.html