mysql - 按多列分组并标记唯一的子组

Question

从这些数据开始:

+--------+------------+--------+--------+
| Fruit  | Vegetables | Colors | Number |
+--------+------------+--------+--------+
| Apple  | Beans      | Blue   |     10 |
| Apple  | Beans      | Blue   |     10 |
| Apple  | Beans      | Blue   |     20 |
| Apple  | Beans      | Blue   |     20 |
| Apple  | Beans      | Green  |     20 |
| Apple  | Beans      | Green  |     20 |
| Banana | Brocoli    | Red    |     10 |
| Banana | Brocoli    | Red    |     10 |
| Banana | Brocoli    | Blue   |     10 |
| Banana | Beans      | Blue   |     20 |
| Banana | Beans      | Green  |     20 |
| Banana | Beans      | Green  |     20 |
+--------+------------+--------+--------+

我想根据水果计算蔬菜、颜色和数字的独特组合。所以我从这个查询开始。

SET @a:=0;

select *, @a:=@a+1 as newid FROM(
select fruit, vegetable, color, number

FROM test

GROUP BY fruit, vegetable, color, number) as info

得到这个数据

+-------+--------+------------+--------+--------+
| newid | Fruit  | Vegetables | Colors | Number |
+-------+--------+------------+--------+--------+
|     1 | Apple  | Beans      | Blue   |     10 |
|     2 | Apple  | Beans      | Blue   |     20 |
|     3 | Apple  | Beans      | Green  |     20 |
|     4 | Banana | Brocoli    | Red    |     10 |
|     5 | Banana | Brocoli    | Blue   |     10 |
|     6 | Banana | Beans      | Blue   |     20 |
|     7 | Banana | Beans      | Green  |     20 |
+-------+--------+------------+--------+--------+

但我的最终目标是到达这里

+----------+--------+------------+--------+--------+
|    ID    | Fruit  | Vegetables | Colors | Number |
+----------+--------+------------+--------+--------+
| Apple-1  | Apple  | Beans      | Blue   |     10 |
| Apple-1  | Apple  | Beans      | Blue   |     10 |
| Apple-2  | Apple  | Beans      | Blue   |     20 |
| Apple-2  | Apple  | Beans      | Blue   |     20 |
| Apple-3  | Apple  | Beans      | Green  |     20 |
| Apple-3  | Apple  | Beans      | Green  |     20 |
| Banana-1 | Banana | Brocoli    | Red    |     10 |
| Banana-1 | Banana | Brocoli    | Red    |     10 |
| Banana-2 | Banana | Brocoli    | Blue   |     10 |
| Banana-3 | Banana | Beans      | Blue   |     20 |
| Banana-4 | Banana | Beans      | Green  |     20 |
| Banana-4 | Banana | Beans      | Green  |     20 |
+----------+--------+------------+--------+--------+

我意识到最终我可以使用 group_concat，但我不确定如何最好地在查询中重新开始计数器。

Answer 1

你有一个好的开始。您对 GROUP BY 的困难是正确的。你可以那样做，但是你需要一个 JOIN 操作返回到原始表，以获取所有行，

这是另一种使用“技巧”的方法:将当前行的值保存在用户定义的变量中，以便可以将这些值与下一行的值进行比较。我们使用条件测试来确定我们是将计数器保留在其现有值，还是将其递增 1，或将其重置为 1。

我们在查询中使用了一个ORDER BY 子句来确保返回的行按照行需要比较的顺序返回；我们将每一行与前一行的值进行比较。

举个例子:

SELECT CONCAT(r.fruit,'-',r.i) AS ID
     , r.fruit
     , r.vegetable
     , r.color
     , r.number
  FROM ( SELECT IF( t.fruit <=> @fruit
                , IF( t.vegetable <=> @vegetable 
                  AND t.color     <=> @color
                  AND t.number    <=> @number
                  , @i := @i + 0
                  , @i := @i + 1
                  )
                , @i := 1
                ) AS i
              , @fruit     := t.fruit      AS fruit
              , @vegetable := t.vegetable  AS vegetable
              , @color     := t.color      AS color
              , @number    := t.number     AS number
           FROM ( SELECT @i         := 1
                       , @fruit     := NULL
                       , @vegetable := NULL
                       , @color     := NULL
                       , @number    := NULL
                ) i
          CROSS
           JOIN test t
          ORDER 
             BY t.fruit
              , t.vegetable
              , t.color
              , t.number
       ) r

别名为 i 的内联 View 初始化我们的用户定义变量(而不是必须依赖单独的语句。)我们并不关心内联 View 返回什么，除了它返回 1行(因为我们指定了 JOIN 操作)；我们真正关心的是它在引用它的查询运行之前得到评估。

要测试的 CROSS JOIN 为我们获取测试中的所有行。

我们指定一个 ORDER BY 子句，以便按指定顺序返回行。这很重要，因为我们要将一行中的值与前一行的值进行比较。

“技巧”是一个表达式，它将当前行(例如 t.fruit)的值与前一行的值进行比较:(例如@fruit。)我们使用一个 bool 表达式，如果所有列都匹配则返回 TRUE ，如果这是真的，我们增加计数器(@i)。否则，我们将计数器重置为 1。

MySQL 不保证求值顺序，但我们观察到 SELECT 列表中的表达式是从左到右求值的，因此我们在执行之前要小心执行条件测试覆盖保存前一行值的用户定义变量。

我们将整个查询包裹在括号中并给它一个别名，这样我们就可以运行另一个外部查询来将生成的计数器连接成一个字符串(根据指定的结果集。)

一些**重要说明((:

此语法特定于 MySQL；不能保证我们观察到的行为，但这是我们最接近于模拟其他数据库中可用的“分析函数”的行为。

请注意，此行为可能会在 MySQL 的 future 版本中发生变化。