基于多列排序：判断Disconnect是否为账户最后订单的SQL查询需求

先纠正你原查询里的几个问题：

• 未按账户分区，导致排名是全局排序而非每个账户内部的独立排序
• 字段名不匹配（原表字段是Account no、Order、Order_date，你误用了account_no、order_id、created_t）
• 排序方向错误：要判断最后一笔订单，需按日期降序排序，这样排名为1的就是该账户最新的订单

以下是满足需求的SQL查询：

WITH account_order_ranks AS (
    SELECT 
        `Account no` AS account_no,
        `Order` AS order_type,
        Order_date,
        -- 按账户分区，订单日期降序排名，同日期订单排名一致
        RANK() OVER (PARTITION BY `Account no` ORDER BY STR_TO_DATE(Order_date, '%d-%m-%Y') DESC) AS rank_no
    FROM your_table_name
)
SELECT 
    account_no,
    order_type,
    Order_date,
    CASE 
        WHEN order_type = 'Disconnect' AND rank_no = 1 THEN 'Disconnect是最后一笔订单'
        WHEN order_type = 'Disconnect' AND rank_no > 1 THEN 'Disconnect之后还有其他订单'
        ELSE '非Disconnect订单'
    END AS status
FROM account_order_ranks;

关键说明： #

• PARTITION BY Account no``确保每个账户的订单单独排序
• STR_TO_DATE用于将字符串格式的日期转换为可正确排序的日期类型（若你的数据库中Order_date已是日期类型，可省略该函数）
• 按Order_date降序排序后，rank_no = 1对应该账户的最新订单
• 通过CASE语句直接输出每个Disconnect订单的状态

如果只需筛选Disconnect订单并判断后续是否有订单，可简化查询：

WITH account_order_ranks AS (
    SELECT 
        `Account no` AS account_no,
        `Order` AS order_type,
        Order_date,
        RANK() OVER (PARTITION BY `Account no` ORDER BY STR_TO_DATE(Order_date, '%d-%m-%Y') DESC) AS rank_no
    FROM your_table_name
)
SELECT 
    account_no,
    Order_date AS disconnect_date,
    CASE WHEN rank_no = 1 THEN '无后续订单' ELSE '有后续订单' END AS has_following_orders
FROM account_order_ranks
WHERE order_type = 'Disconnect';

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