mysql - 如何将此查询从 MySQL 转换为 SQL Server？

Question

这是源表:

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

这是结果表:

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

我有 MySQL 版本查询，如何将其转换为 SQL Server 版本？我尝试进行声明，但我不知道如何更新变量的值。

SELECT Score, ranking AS Rank
FROM
(
SELECT 
    Score,
    CASE
        WHEN @dummy = Score 
        THEN @ranking := @ranking
        ELSE @ranking := @ranking + 1
    END as ranking,
    @dummy := Score
FROM Scores, (SELECT @ranking := 0, @dummy := -1) init 
ORDER BY Score DESC
)AS Result

Answer 1

您的代码是 ANSI 标准 DENSE_RANK() 函数的 MySQL 变通方法(如 Sean Lange 在评论中所解释的)。代码看起来很简单:

SELECT s.*, DENSE_RANK() OVER (ORDER BY Score DESC) as rank
FROM Scores s
ORDER BY Score DESC;

顺便说一句，MySQL 代码本身并不是很准确。以下要安全得多:

SELECT Score, ranking AS Rank
FROM (SELECT Score,
             (@rn := if(@dummy = Score, @rn,
                             if(@dummy := Score, @rn + 1, @rn + 1)
                            )
             ) as ranking
      FROM Scores CROSS JOIN
           (SELECT @rn := 0, @dummy := -1) init 
      ORDER BY Score DESC
     ) s;

主要区别在于所有对变量的赋值都发生在单个表达式中。 MySQL 不保证 SELECT 中表达式的求值顺序，因此您不应在一个表达式中使用 @dummy 然后在另一个表达式中赋值。