MySQL分组连续出现

我想按列值的持续呈现将查询功效分组.假设我有一张表格,个中列出了每年角逐的得胜者,如下所示：

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

只要我有开始和竣事或范畴(譬喻,可以行使GROUP_CONCAT天生2004,2005,2006而不是2004-2006,我就不消太担忧“ start_end”的名目,那如故可以) ). 最佳谜底 只要您的表格如下所示：

"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.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!