MySQL Gaps and Islands问题：求解员工抛硬币最长连续正面次数

核心计算逻辑 #

你当前的思路是正确的，间隙与岛屿问题的核心就是利用两个行号的差值作为连续相同值的分组标识：

• 同一条记录的全局序列值减去按抛硬币结果分组的序列值，对于同一连续批次的相同结果来说差值固定，以此值作为连续分组ID即可区分不同的连续段

单员工查询实现（以Abe为例） #

基于你现有代码扩展，直接返回Abe的最长连续正面次数：

WITH grouped_flips AS (
    SELECT 
        time_of_flip, 
        flip_result,
        ROW_NUMBER() OVER (ORDER BY time_of_flip) AS continues_seq,
        ROW_NUMBER() OVER (PARTITION BY flip_result ORDER BY time_of_flip) AS seq_by_value,
        ROW_NUMBER() OVER (ORDER BY time_of_flip) - ROW_NUMBER() OVER (PARTITION BY flip_result ORDER BY time_of_flip) AS group_id
    FROM coin_flipping
    WHERE flipper = 'Abe'
)
SELECT MAX(continuous_count) AS max_consecutive_h
FROM (
    SELECT group_id, COUNT(*) AS continuous_count
    FROM grouped_flips
    WHERE flip_result = 'H'
    GROUP BY group_id
) t;

全员工适配查询实现 #

要适配所有员工，只需要将所有窗口函数的分区条件增加flipper字段，保证每个员工的抛硬币记录独立计算序列即可：

WITH all_grouped_flips AS (
    SELECT 
        flipper,
        time_of_flip, 
        flip_result,
        ROW_NUMBER() OVER (PARTITION BY flipper ORDER BY time_of_flip) AS continues_seq,
        ROW_NUMBER() OVER (PARTITION BY flipper, flip_result ORDER BY time_of_flip) AS seq_by_value,
        ROW_NUMBER() OVER (PARTITION BY flipper ORDER BY time_of_flip) - ROW_NUMBER() OVER (PARTITION BY flipper, flip_result ORDER BY time_of_flip) AS group_id
    FROM coin_flipping
)
SELECT 
    flipper, 
    COALESCE(MAX(continuous_count), 0) AS max_consecutive_h
FROM (
    SELECT 
        flipper, 
        group_id, 
        COUNT(*) AS continuous_count
    FROM all_grouped_flips
    WHERE flip_result = 'H'
    GROUP BY flipper, group_id
) t
RIGHT JOIN (SELECT DISTINCT flipper FROM coin_flipping) f ON t.flipper = f.flipper
GROUP BY flipper
ORDER BY max_consecutive_h DESC;

注：上述SQL中使用RIGHT JOIN关联全量员工列表，搭配COALESCE函数可以将从未抛出正面的员工的最长连续次数默认设为0，符合统计逻辑。

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