mysql中连续出现,MySQL组连续出现

I would like to group query results by consecutive appearances of a column values. Let's say I have a table which lists the winners of a competition for each year as follows:

year team_name

2000 AAA

2001 CCC

2002 CCC

2003 BBB

2004 AAA

2005 AAA

2006 AAA

I would like a query which outputs:

start_end total team_name

2000 1 AAA

2001-2002 2 CCC

2003 1 BBB

2004-2006 3 AAA

I'm not too much worried about the format of "start_end" at long as I have the start and end or range (.e.g. one could use GROUP_CONCAT to produce 2004,2005,2006 instead of 2004-2006 and that would still be OK).

解决方案

Provided that your table looks like this :

"id";"year";"team"

"1";"2000";"AAA"

"2";"2001";"CCC"

"3";"2002";"CCC"

"4";"2003";"BBB"

"5";"2004";"AAA"

"6";"2005";"AAA"

"7";"2006";"AAA"

This query should do the trick :

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, x.team, 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, x.team, 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, x.team, 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;

BTW You might find the Common Queries Tree handy to solve these problems (check the answers for "Find previous and next values in a sequence") :p.