在MySQL中将相似的行分组 [英] Grouping similar rows next to each other in MySQL

问题描述

我不知道如何解释这一点，所以请耐心等待。

I have no idea how to explain this well, so please bear with me.

我试图将彼此相邻的相似行分组，忽略第n + 1行如果它是相同的。我不确定这是否容易在MySQL中完成。这些行不共享描述以外的其他属性。如果还有其他重复的描述不相邻，我仍然希望将它们返回。

I am trying to group similar rows that are right next to each other, essentially ignoring the n+1th row if it's the same. I'm not sure if this is easy to do in MySQL or not. These rows share no other attribute other than the description. If there are other duplicate "descriptions" that are NOT next to each other, I still want them to be returned.

我有一个表中充满了与此类似的条目：

I have a table full of entries similar to this:

+--+-------------------------+
|id|description              |
+--+-------------------------+
| 1|hello                    |
+--+-------------------------+
| 2|foobar                   |  \_   Condense these into one row
+--+-------------------------+  /
| 3|foobar                   |
+--+-------------------------+
| 4|hello                    |
+--+-------------------------+
| 5|world                    |  \__   Condense these into a row
+--+-------------------------+  /
| 6|world                    |
+--+-------------------------+
| 7|puppies                  |
+--+-------------------------+
| 8|kittens                  |  \__   These too...
+--+-------------------------+  /
| 9|kittens                  |
+--+-------------------------+
|10|sloths                   |
+--+-------------------------+
|11|kittens                  |
+--+-------------------------+

推荐答案

您可以使用巧妙的技巧来做到这一点。诀窍是从描述的 id 中计算描述的数量，直到与不同的特定id。对于序列中的值，这个数字将是相同的。

You can do this by using a clever trick. The trick is to count the number of descriptions up to a particular id that are different from the description at that id. For values in a sequence, this number will be the same.

在MySQL中，您可以使用相关子查询来进行此计数。其余的只是按照这个字段进行分组，以便将值集中在一起：

In MySQL you can do this count using a correlated subquery. The rest is just grouping by this field to bring the values together:

select min(id) as id, description, count(*) as numCondensed
from (select t.*,
             (select count(*)
              from table t2
              where t2.id <= t.id and t2.description <> t.description
             ) as grp
      from table t
     ) t
group by description, grp;

这篇关于在MySQL中将相似的行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！