按多列分组和标记唯一子组。

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

首先，这会为你重新设定一个不同的水果的数量。

SELECT *, 
       if(@a = fruit, @b := @b + 1, @b := 1) as counter,
       @a := fruit
FROM (
    select fruit, vegetable, color, number
    FROM test
    GROUP BY fruit, vegetable, color, number
) t
CROSS JOIN (SELECT @a := null, @b := null)temp

但是要提供一个结果给你所期望的结果。尝尝这个

SELECT 
    CONCAT(fruit, ' ', counter) as fruit, vegetable, color, number
FROM
(   SELECT 
        *, 
        if(@a = fruit, if(@c = color AND @d = number, @b, @b := @b + 1), @b := 1) as counter,
        @a := fruit, @c := color, @d := number
    FROM test
    CROSS JOIN (SELECT @a := null, @b := null, @c := null, @d := null)temp
)t

基本上，对于第二个查询，您可以逐行进行比较，以查看上一行中的值是否为下一行中的值，如果没有，则重置计数器，如果它们随后不再重置而是递增，如果颜色和数字都匹配上一行，则只需输入该计数器值而不增加它。

演示

您必须在这里进行的关键评估是，在对计数器进行条件检查之后，将赋值分配给列变量。这样您就可以将前面的行值与当前的行值进行比较.有一点要注意的是，MySQL并不保证这种评估顺序会以这种方式发生。但通常从左到右计算。

Answer 2

你的开局很好。你说得对，和大家在一起的困难。您可以这样做，但是您需要返回到原始表的联接操作，以获取所有行，

下面是另一种使用“技巧”的方法:将当前行的值保存在用户定义的变量中，以便将这些值与下一行的值进行比较。我们使用条件测试来确定是将计数器保留在其现有值上，还是将其增加一倍，或者将其重置为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别名的内联视图初始化用户定义的变量(而不是依赖于单独的语句)。除了返回1行(因为我们指定了一个联接操作)外，我们实际上并不关心内联视图返回什么；我们真正关心的是，在运行它的查询之前对它进行评估。

要测试的交叉连接将使我们从测试中获得所有行。

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

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

MySQL并不保证计算的顺序，但是我们注意到SELECT列表中的表达式是从左到右计算的，所以在重写保存上一行值的用户定义变量之前，我们要小心地执行条件测试。

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

一些**重要说明((：

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

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