mysql中连续出现_MySQL分组连续出现

只要您的表格如下所示：

"id";"year";"team"

"1";"2000";"AAA"

"2";"2001";"CCC"

"3";"2002";"CCC"

"4";"2003";"BBB"

"5";"2004";"AAA"

"6";"2005";"AAA"

"7";"2006";"AAA"

这个查询应该可以解决这个问题：

SELECT a.year AS start,MIN(c.year) AS end,MIN(c.year)-a.year+1 AS total,CONCAT_WS('-',a.year,IF(a.year = min(c.year),NULL,min(c.year))) as start_end,a.team

FROM

( SELECT x.year,x.team,COUNT(*) id

FROM results x

JOIN results y

ON y.year <= x.year

GROUP BY x.id

) AS a

LEFT JOIN

( SELECT x.year,COUNT(*) id

FROM results x

JOIN results y

ON y.year <= x.year

GROUP BY x.id

) AS b ON a.id = b.id + 1 AND b.team = a.team

LEFT JOIN

( SELECT x.year,COUNT(*) id

FROM results x

JOIN results y

ON y.year <= x.year

GROUP BY x.id

) AS c ON a.id <= c.id AND c.team = a.team

LEFT JOIN

( SELECT x.year,COUNT(*) id

FROM results x

JOIN results y

ON y.year <= x.year

GROUP BY x.id

) AS d ON c.id = d.id - 1 AND d.team = c.team

WHERE b.id IS NULL AND c.id IS NOT NULL AND d.id IS NULL

GROUP BY start;

顺便说一句,您可能会发现Common Queries Tree可以轻松解决这些问题(请查看“查找序列中的上一个和下一个值”的答案)：p.