选择具有公共属性的连续记录组? [英] Selecting groups of consecutive records with a common attribute?

问题描述

我正在寻找一个能够从单个表中进行选择的查询，该查询将属性相等的连续记录折叠在一起.与分组方式相似，但我希望将每个连续出现的范围分组，而不是将属性的每个出现分组到一起.

I'm looking for a query capable of selecting from a single table in such a way that consecutive records for which an attribute is equal are collapsed together. Similar to group by, but instead of grouping every occurence of the attribute together, I want one group for each consecutive range.

示例表:

+-----+-----+
|order|group|
+-----+-----+
|1    |aaa  |
+-----+-----+
|2    |aaa  |
+-----+-----+
|3    |bbb  |
+-----+-----+
|4    |aaa  |
+-----+-----+
|5    |aaa  |
+-----+-----+
|6    |aaa  |
+-----+-----+
|7    |ccc  |
+-----+-----+
|8    |aaa  |
+-----+-----+

预期结果示例:

+-----+-------------------+
|group|group_concat(order)|
+-----+-------------------+
|aaa  |1,2                |
+-----+-------------------+
|bbb  |3                  |
+-----+-------------------+
|aaa  |4,5,6              |
+-----+-------------------+
|ccc  |7                  |
+-----+-------------------+
|aaa  |8                  |
+-----+-------------------+

我不能使用存储过程.

我有一个模糊的概念，我将需要至少一层嵌套来对表进行排序(总共可能更多)，并且可能必须使用变量，但仅此而已.如果您需要更多详细信息，请告诉我.

I have a vague notion I will need at least one level of nesting for sorting the table (probably more in total), and probably have to use variables, but no more than that. Please let me know if you need further details.

创建示例的查询:

create temporary table tab (
    ord int,
    grp varchar(8)
);

insert into tab (ord, grp) values
(1, 'aaa'),
(2, 'aaa'),
(3, 'bbb'),
(4, 'aaa'),
(5, 'aaa'),
(6, 'aaa'),
(7, 'ccc'),
(8, 'aaa');

推荐答案

您可以尝试吗?您可以在此处进行测试 http://www.sqlfiddle.com/#!2/57967/12 .

Could you try this? You can test here http://www.sqlfiddle.com/#!2/57967/12.

Select grp_new, group_concat(ord)
From (
   Select ord, if(grp = @prev, @seq, @seq := @seq + 1) as seq,
    if(grp = @prev, grp, @prev := grp) as grp_new
  From tab, (SELECT @seq := 0, @prev := '') AS init
  Order by ord
) x
Group by grp_new, seq;

关键思想是为相同的连续组生成相同的seq，如下所示.

The key idea is generate same seq for same consecutive group as follows.

Select
   ord, if(grp = @prev, @seq, @seq := @seq + 1) as seq,
    if(grp = @prev, grp, @prev := grp) as grp_new
From tab, (SELECT @seq := 0, @prev := '') AS init
Order by ord

然后最终将GROUP BY grp, seq分组，即使它们具有相同的grp，也可以区分每个连续的组.

then finally grouping GROUP BY grp, seq which can differenciate each consecutive groups even if they have same grp.

要获得示例中的准确结果，

To get exactly the result in the example:

Select grp_new, group_concat(ord order by ord)
From (
  Select ord, if(grp = @prev, @seq, @seq := @seq + 1) as seq,
    if(grp = @prev, grp, @prev := grp) as grp_new
  From tab, (SELECT @seq := 0, @prev := '') AS init
  Order by ord
) x
Group by seq

这篇关于选择具有公共属性的连续记录组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！