求含Rank() Over的MSSQL查询语句的MySQL等价实现

没问题，我来帮你把这段MSSQL查询转换成等价的MySQL语句，核心是处理分组排名的兼容问题，分两种场景给你方案：

1. MySQL 8.0+ 版本（推荐，语法最贴近原MSSQL） #

MySQL 8.0及以上已经支持标准的窗口函数，和MSSQL的RANK()语法几乎完全一致，直接转换即可：

SELECT DISTINCT
    inv.name AS InvName,
    slotapproval.value,
    RANK() OVER (PARTITION BY inv.name, slots.customerid ORDER BY CAST(slots.date AS DATE) DESC) AS RankDESC,
    RANK() OVER (PARTITION BY inv.name, slots.customerid ORDER BY CAST(slots.date AS DATE) ASC) AS RankASC
FROM slotapproval
INNER JOIN slots ON slots.slotid = slotapproval.slotid
INNER JOIN inv ON slotapproval.invid = inv.invid;

细节说明： #

• RANK()的行为和MSSQL完全一致：遇到相同日期时会跳过后续排名（比如两个并列第1，下一个结果会是第3）；如果需要并列时不跳号（比如并列第1后直接是第2），可以替换成DENSE_RANK()
• 如果slots.date本身就是DATE类型，CAST(slots.date AS DATE)可以直接省略，用slots.date即可
• 原查询的DISTINCT：如果关联后的结果本身没有重复行，其实可以去掉；如果存在重复数据，保留它就能得到唯一的InvName、value和对应排名组合

2. MySQL 5.x 版本（不支持窗口函数，用变量模拟） #

如果你的MySQL版本低于8.0，需要用用户变量手动模拟分组排名逻辑：

SELECT DISTINCT
    InvName,
    value,
    RankDESC,
    RankASC
FROM (
    SELECT
        inv.name AS InvName,
        slotapproval.value,
        -- 模拟降序RANK()
        @rank_desc := CASE
            WHEN @prev_name = inv.name AND @prev_customer = slots.customerid THEN 
                IF(@prev_date = CAST(slots.date AS DATE), @rank_desc, @rank_desc + @row_num)
            ELSE 1
        END AS RankDESC,
        -- 模拟升序RANK()
        @rank_asc := CASE
            WHEN @prev_name = inv.name AND @prev_customer = slots.customerid THEN 
                IF(@prev_date_asc = CAST(slots.date AS DATE), @rank_asc, @rank_asc + @row_num_asc)
            ELSE 1
        END AS RankASC,
        -- 更新分组跟踪变量
        @prev_name := inv.name,
        @prev_customer := slots.customerid,
        @prev_date := CAST(slots.date AS DATE),
        @prev_date_asc := CAST(slots.date AS DATE),
        -- 记录同组内的行号（用于实现RANK()跳号逻辑）
        @row_num := CASE
            WHEN @prev_name = inv.name AND @prev_customer = slots.customerid THEN @row_num + 1
            ELSE 1
        END,
        @row_num_asc := CASE
            WHEN @prev_name = inv.name AND @prev_customer = slots.customerid THEN @row_num_asc + 1
            ELSE 1
        END
    FROM slotapproval
    INNER JOIN slots ON slots.slotid = slotapproval.slotid
    INNER JOIN inv ON slotapproval.invid = inv.invid
    -- 必须按分区字段排序，保证变量分组逻辑正确
    ORDER BY inv.name, slots.customerid, CAST(slots.date AS DATE) DESC
    -- 初始化所有变量
    CROSS JOIN (SELECT @prev_name := '', @prev_customer := '', @prev_date := '', @rank_desc := 0, @row_num := 0,
                       @prev_date_asc := '', @rank_asc := 0, @row_num_asc := 0) AS vars
) AS ranked_data;

细节说明： #

• 变量需要按分组字段（inv.name、slots.customerid）排序，否则分组逻辑会出错
• 如果需要模拟DENSE_RANK()，只需要去掉@row_num相关的判断，并列时直接保持当前排名即可

内容的提问来源于stack exchange，提问作者PK_Ask