mysql - 在 MySQL 中使用变量模拟 MS SQL Server ROW_NUMBER() 功能的可靠性和 ANSI-SQL 合规性？

Question

正如本论坛其他地方所讨论的(例如 here)，可以使用 MySQL 中的变量来模拟 row_number() over (partition by ... order by ...) MS SQL Server 中的功能。例如，这样的代码:

SELECT 
    col1, 
    col2, 
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS rowNum
FROM Table1

可以这样在MySQL中实现:

SELECT 
    @rowNum := 
        CASE 
            WHEN col1<>@col1 OR col2<>@col2 THEN 1 
            ELSE @rowNum+1 
        END rowNum
    , @col1 := col1 col1
    , @col2 := col2 col2
    FROM
    (SELECT @col1 := 'xxx', @col2 := 'yyy', @rowNum := 0) a,
    (SELECT col1, col2, col3 FROM Table1 ORDER BY col1, col2, col3 DESC) b

虽然这非常快(因为它避免了自连接)，但我对将其投入生产犹豫不决，因为它对可能可移植也可能不可移植的查询执行计划做出了详细的假设。特别是，它假设:

1) 表 'b' 的行由外部查询按排序顺序处理，

2) 外部查询的列从左到右计算(即 rowNum -> col1 -> col2。

这些假设似乎打破了我习惯的 SQL 语法的通常抽象。此外，此查询要求程序员确保他/她为 col1 和 col2 提供的初始值不存在于 Table1 中。同样，我认为这是糟糕的设计。这让我想到了一个相关的问题，后一种形式(使用变量)是否符合 ANSI SQL(92 或 99)，或者它是 MySQL 的一个特性？确定给定语法是否符合 ANSI SQL 标准的明确来源是什么？您是否愿意将第二个版本投入生产？

Answer 1

不，MySQL 解决方案不是标准 SQL。 MySQL 用户变量是 ANSI/ISO SQL 的扩展。

SQL-99 Complete, Really是一个以人类可读的方式描述 SQL 规范的免费在线资源。这是一个在线版本，希望在其作者的许可下发布，作者 the book by the same name .

但是，我不知道 SQL:2003 或更高版本的类似资源。你必须 purchase the specification documents from ISO .它们并不便宜，而且是干读物。

恕我直言，试图将自己限制为 100% 可移植且符合语言抽象的 SQL 太过拘束。例如，尝试找到在所有品牌的 RDBMS 中实现完全相同并且完全匹配语言规范的一种 SQL 数据类型。我还没找到。