我如何在MySQL中做一个连续的组？ [英] How can I do a contiguous group by in MySQL?

问题描述

如何在MySQL中返回实际上是连续GROUP BY的内容。换句话说，一个尊重记录集顺序的GROUP BY？

例如， SELECT MIN（col1），col2，COUNT（*） FROM table下面的表GROUP1 col2 ORDER BY col1 ，其中col1是一个唯一的有序索引：

 
 1 a 
 2 a 
 3 b 
 4 b 
 5 a 
 6 a 

收益：

 1 a 4 
 3 b 2 

但我需要返回以下内容：

 1 a 2 
 3 b 2 
 5 a 2 

解决方案

使用：

  SELECT MIN（t.id ）'mi'，
 t.val，
 COUNT（*）
 FROM（SELECT x.id，
 x.val，
 CASE 
 WHEN xt.val IS NULL OR xt.val！= x.val THEN 
 @rownum：= @ rownum + 1 
 ELSE 
 @rownum 
 END AS grp 
 FROM TABLE x 
 JOIN（SELECT @rownum：= 0）r 
 LEFT JOIN（SELECT t.id +1'id'，
 t.val 
 FROM TABLE t）xt ON xt.id = x.id）t 
 GROUP BY t.val，t.grp 
 ORDER BY mi 
  

这里的关键是创建一个可以进行分组的人为值。

以前，修正的Guffa答案：

pre $ SELECT t.id，t.val
FROM TABLE t
LEFT JOIN TABLE t2 on t2.id + 1 = t.id
WHERE t2.val IS NULL
OR t.val<> t2.val


How can I return what would effectively be a "contiguous" GROUP BY in MySQL. In other words a GROUP BY that respects the order of the recordset?

For example, SELECT MIN(col1), col2, COUNT(*) FROM table GROUP BY col2 ORDER BY col1 from the following table where col1 is a unique ordered index:

1    a
2    a
3    b
4    b
5    a
6    a

returns:

1    a    4
3    b    2

but I need to return the following:

1    a    2
3    b    2
5    a    2

解决方案

Use:

   SELECT MIN(t.id) 'mi', 
          t.val, 
          COUNT(*)
     FROM (SELECT x.id, 
                 x.val, 
                 CASE 
                   WHEN xt.val IS NULL OR xt.val != x.val THEN 
                     @rownum := @rownum+1 
                   ELSE 
                     @rownum 
                 END AS grp
            FROM TABLE x
            JOIN (SELECT @rownum := 0) r
       LEFT JOIN (SELECT t.id +1 'id',
                         t.val
                    FROM TABLE t) xt ON xt.id = x.id) t
 GROUP BY t.val, t.grp
 ORDER BY mi

The key here was to create an artificial value that would allow for grouping.

Previously, corrected Guffa's answer:

   SELECT t.id, t.val
     FROM TABLE t
LEFT JOIN TABLE t2 on t2.id + 1 = t.id
    WHERE t2.val IS NULL 
       OR t.val <> t2.val

这篇关于我如何在MySQL中做一个连续的组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！