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

Stack Overflow用户

提问于 2014-09-08 17:57:00

回答 2查看 74关注 0票数 1

从这些数据开始：

+--------+------------+--------+--------+
| 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，但我不知道如何最好地在查询中重新开始计数器。

mysql

sql

group-by

语音识别特惠，低至14.9元！

提供业界非常具有性价比的语音识别服务，超高识别准确率，适用多场景

回答 2

Stack Overflow用户

回答已采纳

发布于 2014-09-08 18:28:05

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

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并不保证这种评估顺序会以这种方式发生。但通常从左到右计算。

票数 1

Stack Overflow用户

发布于 2014-09-08 18:29:41

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

下面是另一种使用“技巧”的方法:将当前行的值保存在用户定义的变量中，以便将这些值与下一行的值进行比较。我们使用条件测试来确定是将计数器保留在其现有值上，还是将其增加一倍，或者将其重置为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