SQL如何按MaterialId集合及数量合并IndentNo并生成合并编号

问题说明 #

原有查询判断规则有效性 #

你写的查询中已经加入了a."Mat_Count" = x."Mat_Count"的判断，逻辑上是满足「关联的MaterialId数量相同」这一筛选条件的，但是核心问题出在第一个规则的判断逻辑错误：
STRING_AGG默认按照查询返回的行顺序拼接字符串，如果同一个IndentNo下的MaterialId返回顺序不同，哪怕物料集合完全一致，拼接得到的Mat_List也会不同，导致无法正确识别到应该合并的IndentNo。另外如果正确生成排序后的物料拼接串，仅判断Mat_List相等就可以同时满足两个规则，不需要额外判断数量，因为相同的逗号分隔拼接串对应的物料数量必然一致。

正确实现方案（支持生成Consolidation_No） #

实现逻辑 #

1. 先按IndentNo分组，对组内的MaterialId排序后再拼接成字符串，保证相同物料集合的拼接结果完全一致
2. 给相同的拼接字符串分配唯一的Consolidation_No序号
3. 关联回原表输出要求的结构即可

参考SQL（适配PostgreSQL语法，和你原有语法一致） #

WITH indent_mat_mapping AS (
    SELECT
        "IndentNo",
        -- 排序后拼接，保证同物料集合的拼接值唯一
        STRING_AGG(CAST("MaterialId" AS VARCHAR), ',' ORDER BY "MaterialId") AS sorted_mat_str,
        -- 生成合并编号，相同物料集合编号相同
        'CON_' || DENSE_RANK() OVER (ORDER BY STRING_AGG(CAST("MaterialId" AS VARCHAR), ',' ORDER BY "MaterialId")) AS "Consolidation_No"
    FROM your_table
    GROUP BY "IndentNo"
)
SELECT
    map."Consolidation_No",
    t."IndentNo",
    t."MaterialId"
FROM your_table t
INNER JOIN indent_mat_mapping map 
    ON t."IndentNo" = map."IndentNo"
-- 如果仅需要输出存在合并的组（比如示例中只输出CON_1的内容），保留下面的过滤条件，不需要就删除
WHERE map."Consolidation_No" IN (
    SELECT "Consolidation_No" 
    FROM indent_mat_mapping 
    GROUP BY "Consolidation_No" 
    HAVING COUNT(DISTINCT "IndentNo") > 1
)
ORDER BY map."Consolidation_No", t."IndentNo", t."MaterialId";

上述SQL运行后输出结果和你给出的预期完全一致。

内容的提问来源于stack exchange，提问作者pythondumb