MySQL组连续出现 [英] MySQL group by consecutive appearances

问题描述

我想通过连续出现的列值对查询结果进行分组。假设我有一张表格，列出每年比赛的获胜者如下：

  year team_name 
 2000 AAA 
 2001 CCC 
 2002 CCC 
 2003 BBB 
 2004 AAA 
 2005 AAA 
 2006 AAA 
  

我想要一个查询输出：

  start_end total team_name 
 2000 1 AAA 
 2001-2002 2 CCC 
 2003 1 BBB 
 2004-2006 3 AAA 
  

只要我具有开始和结束或范围（例如.eg可以使用GROUP_CONCAT生成2004,2005，我不太担心start_end的格式， 2006年，而不是2004 - 2006年，这仍然可以）。

解决方案

假设您的表格如下所示：

 id;year;team
1;2000;AAA
2;2001;CCC
3;2002;CCC
4;2003;BBB
5 2004年; AAA
6;2005;AAA
7;2006;AAA
  

这个查询应该做的诀窍是：

$ p $ 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结果y
ON y.year< = x.year
GROUP BY x.id
）作为
LEFT JOIN
（SELECT x.year，x.team，COUNT（*）id
FROM results x
JOIN结果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结果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结果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和c.id IS NOT NULL AND d.id IS NULL
GROUP BY start;


顺便说一句你可能会发现 Common Queries Tree 方便地解决这些问题（查看在序列中查找上一个值和下一个值的答案）：p。

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.

这篇关于MySQL组连续出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！