mysql - MySQL @currant := @currank + 1 in MS SQL? 相当于什么

Question

这是主要的 MySQL 脚本，我尝试将以下脚本转换为 MS SQL 脚本。我不知道等价物。

SELECT 
        (SELECT 
                COALESCE(transaction_price, 0)
            FROM
                (SELECT 
                    COALESCE(transaction_price, 0) AS TRANSACTION_PRICE, COALESCE(actual_size, 0) AS ACTUAL_SIZE, @currank = @currank + 1 AS rank
                FROM
                    dw_property_detail p, (SELECT @currank:=0) r
                WHERE
                    land_id = 2 AND transaction_price IS NOT NULL AND flat_type = 'Studio'
                ORDER BY transaction_price DESC) x
            WHERE
                x.rank = 1) AS TRAN_S, 
        (SELECT 
                COALESCE(per_ft_s, 0)
            FROM
                (SELECT 
                    COALESCE(transaction_price / actual_size, 0) AS PER_FT_S, @currank:=@currank + 1 AS rank
                FROM
                    dw_property_detail p, (SELECT @currank:=0) r
                WHERE
                    land_id = 2 AND transaction_price IS NOT NULL AND flat_type = 'Studio'
                ORDER BY COALESCE(transaction_price / actual_size, 0) DESC) x
            WHERE
                x.rank = 1) AS PER_FT_S

@currank := @currank + 1 和 SELECT @currank := 0 等价于什么？

Answer 1

ROW_NUMBER(窗口/分析函数 - MySQL 目前不支持的功能)

https://msdn.microsoft.com/en-us/library/ms186734.aspx

将其作为附加列添加到 SELECT 子句中。
为此，您现在不需要查询的 ORDER BY(但如果您想要有序结果，请保留它)

row_number() over (order by transaction_price desc)

<小时/>

SELECT      COALESCE(transaction_price, 0)                          AS TRANSACTION_PRICE
           ,COALESCE(actual_size, 0)                                AS ACTUAL_SIZE
           ,row_number () over (order by transaction_price DESC)    AS rank

FROM        dw_property_detail p

WHERE       land_id     = 2 
        AND flat_type   = 'Studio'
        AND transaction_price IS NOT NULL 

ORDER BY    transaction_price DESC