mysql - 如何在MySQL SELECT中按“行集”分组？

我有表t和列a的组合，这两个列指定了一个b。id和a中的这些组合是重复的。对于每一个这样的组合，我想确定所有的bs。例如：

+------+------+------+
| id   | a    | b    |
+------+------+------+
|    1 | A    | x    |
|    1 | B    | y    |
|    1 | C    | z    |
|    2 | A    | u    |
|    2 | B    | v    |
|    3 | A    | x    |
|    3 | B    | y    |
|    3 | C    | z    |
|    4 | A    | x    |
|    4 | B    | y    |
|    5 | A    | x    |
|    5 | B    | y    |
|    5 | C    | z    |
|    5 | D    | n    |
|    6 | A    | u    |
|    6 | B    | v    |
|    7 | A    | u    |
|    7 | B    | v    |
+------+------+------+

现在我想得到idid和1的组合。我想得到id3，(A,x),(B,y),(C,z)和2的组合。ids6和7与(A,u),(B,v)不同（对于4缺少一行，对于5有一行太多），因此应单独报告：

(A,x),(B,y),(C,z): 1,3
(A,u),(B,v): 2,6,7
(A,x),(B,y): 4
(A,x),(B,y),(C,z),(D,n): 5

如何查询这个分组的MySQL？
用于设置示例表的SQL代码：

CREATE TABLE t (id int, a VARCHAR(4), b VARCHAR(4));
INSERT INTO t VALUES (1, 'A', 'x');
INSERT INTO t VALUES (1, 'B', 'y');
INSERT INTO t VALUES (1, 'C', 'z');
INSERT INTO t VALUES (2, 'A', 'u');
INSERT INTO t VALUES (2, 'B', 'v');
INSERT INTO t VALUES (3, 'A', 'x');
INSERT INTO t VALUES (3, 'B', 'y');
INSERT INTO t VALUES (3, 'C', 'z');
INSERT INTO t VALUES (4, 'A', 'x');
INSERT INTO t VALUES (4, 'B', 'y');
INSERT INTO t VALUES (5, 'A', 'x');
INSERT INTO t VALUES (5, 'B', 'y');
INSERT INTO t VALUES (5, 'C', 'z');
INSERT INTO t VALUES (5, 'D', 'n');
INSERT INTO t VALUES (6, 'A', 'u');
INSERT INTO t VALUES (6, 'B', 'v');
INSERT INTO t VALUES (7, 'A', 'u');
INSERT INTO t VALUES (7, 'B', 'v');

最佳答案

我们可以首先使用id和Concat()得到特定Group_Concat()中的所有组合。我们还可以使用(a,b)和(b,a)函数确保Greatest()与Least()处理相同。注意，如果(b,a)不应被视为(a,b)，则可以在下面的查询中去掉Greatest()和Least()的用法。
在Group_Concat()中，我们还对它们进行排序，这样就不会得到两个单独的组合：(a,b), (c,d)和(c,d), (a,b)。订购确保我们只得到这两种情况下的：(a,b), (c,d)。
最后，在派生表中使用此结果集，现在聚合具有相同组合集的所有id。
模式（MySQL v5.7）

CREATE TABLE t (id int, a VARCHAR(4), b VARCHAR(4));
INSERT INTO t VALUES (1, 'A', 'x');
INSERT INTO t VALUES (1, 'B', 'y');
INSERT INTO t VALUES (1, 'C', 'z');
INSERT INTO t VALUES (2, 'A', 'u');
INSERT INTO t VALUES (2, 'B', 'v');
INSERT INTO t VALUES (3, 'A', 'x');
INSERT INTO t VALUES (3, 'B', 'y');
INSERT INTO t VALUES (3, 'C', 'z');
INSERT INTO t VALUES (4, 'A', 'x');
INSERT INTO t VALUES (4, 'B', 'y');
INSERT INTO t VALUES (5, 'A', 'x');
INSERT INTO t VALUES (5, 'B', 'y');
INSERT INTO t VALUES (5, 'C', 'z');
INSERT INTO t VALUES (5, 'D', 'n');
INSERT INTO t VALUES (6, 'A', 'u');
INSERT INTO t VALUES (6, 'B', 'v');
INSERT INTO t VALUES (7, 'A', 'u');
INSERT INTO t VALUES (7, 'B', 'v');

查询1

SELECT
  dt.combinations,
  GROUP_CONCAT(dt.id) AS ids
FROM
(
SELECT
  GROUP_CONCAT(
    DISTINCT
      CONCAT('(', LEAST(a,b), ',', GREATEST(a,b), ')')
    ORDER BY
      CONCAT('(', LEAST(a,b), ',', GREATEST(a,b), ')') ASC) AS combinations,
  id
FROM t
GROUP BY id
) dt
GROUP BY dt.combinations;

| combinations            | ids   |
| ----------------------- | ----- |
| (A,u),(B,v)             | 2,6,7 |
| (A,x),(B,y)             | 4     |
| (A,x),(B,y),(C,z)       | 1,3   |
| (A,x),(B,y),(C,z),(D,n) | 5     |

View on DB Fiddle