如何构建查询获取排序后数据表末尾连续相同Status的记录数？

需求 #

现有一张包含Sort和Status字段的数据表，需构建查询返回以下两个字段：

• LastStatus：按Sort排序后最后一条记录的Status值
• Times：统计数据表末尾连续与最后一条记录Status值相同的记录数量

示例 #

原始数据表 #

期望查询结果 #

解决方案 #

方法一：使用窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

通过窗口函数标记连续相同Status的分组，再筛选最后一组统计数量：

WITH ranked_data AS (
    SELECT 
        Sort,
        Status,
        SUM(CASE WHEN Status = LAG(Status) OVER (ORDER BY Sort) THEN 0 ELSE 1 END) 
            OVER (ORDER BY Sort) AS group_id
    FROM your_table_name
),
last_group AS (
    SELECT 
        Status AS LastStatus,
        COUNT(*) AS Times
    FROM ranked_data
    WHERE group_id = (SELECT MAX(group_id) FROM ranked_data)
    GROUP BY Status
)
SELECT LastStatus, Times FROM last_group;

逻辑说明

1. ranked_data 公共表表达式：利用LAG()函数获取前一条记录的Status，通过累加生成连续相同Status的分组ID——每遇到不同的Status，分组ID就递增。
2. last_group 公共表表达式：找到最大的分组ID（即最后一组连续相同的Status），统计该分组的记录数，同时取出对应的Status作为LastStatus。
3. 最终查询last_group得到目标结果。

方法二：使用变量（适用于不支持窗口函数的旧版本MySQL，如5.x） #

SELECT 
    @last_status AS LastStatus,
    COUNT(*) AS Times
FROM (
    SELECT 
        Sort,
        Status,
        @group_id := CASE WHEN @prev_status = Status THEN @group_id ELSE @group_id + 1 END AS group_id,
        @prev_status := Status,
        @last_status := Status
    FROM your_table_name,
         (SELECT @group_id := 0, @prev_status := '', @last_status := '') AS init
    ORDER BY Sort
) AS grouped_data
WHERE group_id = (SELECT MAX(group_id) FROM (
    SELECT 
        @group_id_inner := CASE WHEN @prev_status_inner = Status THEN @group_id_inner ELSE @group_id_inner + 1 END AS group_id
    FROM your_table_name,
         (SELECT @group_id_inner := 0, @prev_status_inner := '') AS init_inner
    ORDER BY Sort
) AS temp)
GROUP BY @last_status;

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