SQL中按col2值集合分组col1的需求及查询问题修正

原始表数据 #

需求说明 #

将col1中拥有相同col2值集合的条目分组，期望得到如下结果：

当前问题 #

现有SQL仅返回每组col1的单行结果，不符合需求：

现有SQL代码 #

select listagg(col1, ', ') set1, la as set2
from (
    select LISTAGG(t.col2, ', ') la, t.col1
    from tbl t
    GROUP BY t.col1
    )
GROUP BY la

表结构及插入数据DDL #

CREATE TABLE "TBL" 
(    "COL1" VARCHAR2(20 BYTE), 
     "COL2" VARCHAR2(20 BYTE)
);
INSERT INTO TBL VALUES ('A','1');
INSERT INTO TBL VALUES ('A','2');
INSERT INTO TBL VALUES ('A','3');
INSERT INTO TBL VALUES ('B','1');
INSERT INTO TBL VALUES ('B','2');
INSERT INTO TBL VALUES ('C','1');
INSERT INTO TBL VALUES ('C','2');
INSERT INTO TBL VALUES ('C','3');

修正后的SQL #

WITH col1_groups AS (
    -- 为每个col1生成排序后的col2集合，避免顺序差异导致分组错误
    SELECT 
        col1,
        LISTAGG(col2, ', ') WITHIN GROUP (ORDER BY col2) AS col2_set
    FROM tbl
    GROUP BY col1
),
grouped_col1 AS (
    -- 将拥有相同col2集合的col1聚合为列表
    SELECT 
        LISTAGG(col1, ', ') WITHIN GROUP (ORDER BY col1) AS list1,
        col2_set
    FROM col1_groups
    GROUP BY col2_set
)
-- 关联回原始表，获取每个col2对应的聚合col1列表并去重
SELECT 
    gc.list1,
    t.col2 AS list2
FROM tbl t
JOIN col1_groups cg ON t.col1 = cg.col1
JOIN grouped_col1 gc ON cg.col2_set = gc.col2_set
GROUP BY gc.list1, t.col2
ORDER BY gc.list1, t.col2;

逻辑说明 #

1. col1_groups：为每个col1生成排序后的col2集合字符串，确保元素相同但顺序不同的集合被识别为同一组（比如A和C的col2集合排序后都是1, 2, 3）。
2. grouped_col1：根据col2集合分组，将同组的col1聚合为逗号分隔的列表。
3. 最终关联查询：通过原始表关联分组信息，获取每个col2对应的聚合col1列表，并用GROUP BY去重，得到符合需求的多行结果。

内容的提问来源于stack exchange，提问作者J. Nicholas