MySQL中按Offset排序统计每个playerid至首个dismissaltype为0的行数

问题描述 #

我有一个MySQL数据表，结构和示例数据如下（实际数据量更大）：

需求：针对每个playerid，按Offset列升序排列后，统计从第一行到**首次出现dismissaltype=0**的行数（包含该行）；如果该玩家没有dismissaltype=0的记录，统计结果为0。

预期输出：

注：Offset是整数类型，实际值不连续。

解决方案 #

方法一：窗口函数+CASE判断 #

SELECT
    t.playerid,
    CASE
        WHEN MIN(CASE WHEN dismissaltype = 0 THEN row_num END) IS NULL THEN 0
        ELSE MIN(CASE WHEN dismissaltype = 0 THEN row_num END)
    END AS count
FROM (
    -- 按玩家分组、Offset升序给记录编号
    SELECT
        playerid,
        dismissaltype,
        Offset,
        ROW_NUMBER() OVER (PARTITION BY playerid ORDER BY Offset) AS row_num
    FROM your_table_name -- 替换为你的实际表名
) t
GROUP BY t.playerid;

方法二：COALESCE简化逻辑 #

SELECT
    playerid,
    COALESCE(MIN(IF(dismissaltype = 0, row_num, NULL)), 0) AS count
FROM (
    SELECT
        playerid,
        dismissaltype,
        ROW_NUMBER() OVER (PARTITION BY playerid ORDER BY Offset) AS row_num
    FROM your_table_name -- 替换为你的实际表名
) t
GROUP BY playerid;

逻辑说明 #

1. 子查询层：通过ROW_NUMBER()窗口函数，按playerid分组、Offset升序，为每个玩家的每一条记录分配连续行号row_num，标记该记录在玩家自身序列中的位置。
2. 外层聚合：
   • 筛选所有dismissaltype=0记录对应的row_num，取最小值即为首次出现目标值的行位置。
   • 若玩家无dismissaltype=0记录，MIN()结果为NULL，通过CASE或COALESCE将结果替换为0。

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