如何在 MySQL 中创建一个连续的组?

2021-12-27 00:00:00 sql group-by mysql

如何返回 MySQL 中有效的连续"GROUP BY.换句话说，一个遵守记录集顺序的 GROUP BY?

例如，SELECT MIN(col1), col2, COUNT(*) FROM table GROUP BY col2 ORDER BY col1 来自下表，其中 col1 是唯一的有序索引:

<前>1个2个3 乙4 b5个6个

返回:

1 一个 43 b 2

但我需要返回以下内容:

1 一个 23 b 25 一 2

解决方案

使用:

 SELECT MIN(t.id) 'mi',t.val,数数(*)从(选择 x.id，x.val,案件当 xt.val 为 NULL 或 xt.val != x.val THEN@rownum := @rownum+1别的@rownum结束为 grp从表 x加入(选择@rownum := 0)r左连接(选择 t.id +1 'id',值从表 t) xt ON xt.id = x.id) tGROUP BY t.val, t.grp按米订购

这里的关键是创建一个允许分组的人工值.

之前，更正了古法的回答:

 SELECT t.id, t.val从表 t在 t2.id + 1 = t.id 上左连接表 t2哪里 t2.val 为空或 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

相关文章