MySQL如何匹配指定值后取后续5行并统计各数值出现次数

我们默认示例中的表名为num_records，包含id、number两个字段，以下提供两种适用不同场景的实现方式：

场景1：id为连续自增主键 #

可以直接通过id差值匹配数据，写法最简洁：

SELECT 
  t2.number AS `Number`, 
  COUNT(*) AS `Count`
FROM num_records t1
JOIN num_records t2 
  ON t1.number = 0 
  AND t2.id > t1.id 
  AND t2.id - t1.id <= 5
GROUP BY t2.number
ORDER BY COUNT(*) DESC;

如果需要先查看匹配到的所有明细行，只需去掉外层统计逻辑即可：

SELECT t2.id, t2.number
FROM num_records t1
JOIN num_records t2 
  ON t1.number = 0 
  AND t2.id > t1.id 
  AND t2.id - t1.id <= 5

场景2：id不连续（通用兼容版） #

通过窗口函数生成连续行号再匹配，不受id连续性影响：

WITH ordered_records AS (
  SELECT *, ROW_NUMBER() OVER(ORDER BY id) AS row_num
  FROM num_records
)
SELECT 
  t2.number AS `Number`,
  COUNT(*) AS `Count`
FROM ordered_records t1
JOIN ordered_records t2
  ON t1.number = 0
  AND t2.row_num > t1.row_num
  AND t2.row_num - t1.row_num <= 5
GROUP BY t2.number
ORDER BY COUNT(*) DESC;

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