MySQL - 按行列表中的特定值分组

Question

我有一个行列表，想按 ID 和某个起始值对它们进行分组。 (在下面给出的例子中说 value=1)

按值分组

行集 - MySQL 和 Presto

-----------
ID | value
-----------
A  | 1
A  | 2
B  | 1
B  | 2
B  | 5
B  | 1
B  | 2
C  | 1
C  | 3
C  | 4
C  | 1
D  | 1
D  | 8
D  | 1
-----------

预期输出:

-----------
ID | Value
-----------
A  | 1,2
B  | 1,2,5
B  | 1,2
C  | 1,3,4
C  | 1
D  | 1,8
D  | 1
-----------

实际输出:

-----------
ID | Value
-----------
A  | 1,2
B  | 1,2,5,1,2
C  | 1,3,4,1
D  | 1,8,1
-----------

Answer 1

没有真实身份真的很难。所以我引入rowNumber:

SELECT (@row := @row + 1) AS rowNumber, ID,value
FROM myTable
CROSS JOIN (SELECT @row := 0) AS dummy

然后我添加 2 列，最大 Value:

 SELECT a.*
 FROM customTable as a
 LEFT OUTER JOIN myTable b ON a.id = b.id AND a.value < b.value
 WHERE b.id IS NULL

在此之后我需要使用group_concat(value) 和group by。Group by 有 2 个条件，id 和另一个自定义 bool 字段:

CASE 
   WHEN l1.rowNumber <= l2.rowNumber THEN 0
   ELSE 1
END

最终查询:

SELECT ct1.id, group_concat(ct1.value) as Value
FROM (
         SELECT (@cnt := @cnt + 1) AS rowNumber, ID, value
         FROM myTable
         CROSS JOIN (SELECT @cnt := 0) AS dummy
     ) AS ct1
JOIN (
    SELECT a.*
    FROM (
             SELECT (@row := @row + 1) AS rowNumber, ID, value
             FROM myTable
             CROSS JOIN (SELECT @row := 0) AS dummy
         ) AS a
    LEFT OUTER JOIN myTable b ON a.id = b.id AND a.value < b.value
    WHERE b.id IS NULL
) AS ct2 ON ct2.ID = ct1.id
GROUP BY ct1.id,
         CASE
             WHEN ct1.rowNumber <= ct2.rowNumber THEN 0
             ELSE 1
         END

你可以测试Here .

这只适用于 MySQL 5.6 或更高版本