我正在寻找一个能够从单个表中进行选择的查询,该查询将属性相等的连续记录折叠在一起。与分组方式类似,但我希望将每个连续范围分为一组,而不是将属性的每个出现分组在一起。
表格示例:
+-----+-----+
|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 |
+-----+-------------------+
我不能使用存储过程。
我有一个模糊的概念,我将需要至少一层嵌套来对表进行排序(总计可能更多),并且可能必须使用变量,但仅此而已。如果您需要更多详细信息,请告诉我。
编辑:创建示例的查询:
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进行测试。
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
为相同的连续组生成相同的内容,如下所示。
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
。
编辑:为了获得确切的结果在示例中:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句