Oracle SQL V11：统计拥有相同产品组合的分区门店数量

针对你在PL/SQL 11g中需要找出与指定分区门店产品组合完全相同的其他门店并统计数量的需求，我提供两种实用的SELECT语句实现方案，你可以根据实际场景选择：

方法一：使用LISTAGG聚合（简洁高效，适合产品数量不多的场景） #

Oracle 11g支持LISTAGG函数，可以将每个门店的产品按排序后拼接成唯一字符串，相同的产品组合会生成完全一致的字符串。结合产品数量双重验证，能精准匹配目标门店：

WITH target_store AS (
    -- 获取目标门店的产品组合特征：排序后的产品字符串 + 产品总数
    SELECT 
        LISTAGG(Product, ',') WITHIN GROUP (ORDER BY Product) AS product_list,
        COUNT(*) AS product_count
    FROM your_table  -- 替换成你的实际表名
    WHERE Area = 10 AND Store = 1  -- 指定目标门店
),
all_stores AS (
    -- 计算所有门店的产品组合特征
    SELECT 
        Area,
        Store,
        LISTAGG(Product, ',') WITHIN GROUP (ORDER BY Product) AS product_list,
        COUNT(*) AS product_count
    FROM your_table
    GROUP BY Area, Store
)
-- 统计匹配的门店数量（排除目标门店本身）
SELECT COUNT(*) AS matching_stores_count
FROM all_stores
WHERE 
    product_list = (SELECT product_list FROM target_store)
    AND product_count = (SELECT product_count FROM target_store)
    AND NOT (Area = 10 AND Store = 1);

效果说明 #

根据你提供的样本数据，目标门店（Area 10, Store 1）的产品是A、B，最终统计结果会是2（匹配的门店是Area 10 Store 3和Area 13 Store 1）。

方法二：集合运算（无长度限制，适合产品数量较多的场景） #

如果门店产品数量较多，LISTAGG拼接的字符串可能超过VARCHAR2的4000字符限制，这时可以用集合的双向包含验证来判断产品组合是否完全一致：

WITH target_products AS (
    -- 获取目标门店的所有产品
    SELECT Product FROM your_table WHERE Area = 10 AND Store = 1
),
all_store_products AS (
    -- 所有门店的产品数据
    SELECT 
        Area,
        Store,
        Product
    FROM your_table
)
-- 统计满足双向包含的门店数量
SELECT COUNT(DISTINCT Area || '-' || Store) AS matching_stores_count
FROM all_store_products asp
WHERE 
    -- 目标门店的所有产品都在当前门店中存在
    NOT EXISTS (
        SELECT 1 FROM target_products tp 
        WHERE tp.Product NOT IN (SELECT Product FROM all_store_products WHERE Area = asp.Area AND Store = asp.Store)
    )
    -- 当前门店的所有产品都在目标门店中存在
    AND NOT EXISTS (
        SELECT 1 FROM all_store_products asp2 
        WHERE asp2.Area = asp.Area AND asp2.Store = asp.Store 
        AND asp2.Product NOT IN (SELECT Product FROM target_products)
    )
    -- 排除目标门店本身
    AND NOT (asp.Area = 10 AND asp.Store = 1);

逻辑说明 #

两个NOT EXISTS条件同时满足时，说明两个门店的产品集合完全等价——既没有目标门店有但当前门店没有的产品，也没有当前门店有但目标门店没有的产品。

PL/SQL块中调用（动态指定目标门店） #

如果需要在PL/SQL程序中动态传入目标门店参数，可以用绑定变量实现：

DECLARE
    v_target_area  NUMBER := 10;   -- 可替换为动态参数
    v_target_store NUMBER := 1;
    v_match_count  NUMBER;
BEGIN
    WITH target_store AS (
        SELECT 
            LISTAGG(Product, ',') WITHIN GROUP (ORDER BY Product) AS product_list,
            COUNT(*) AS product_count
        FROM your_table
        WHERE Area = v_target_area AND Store = v_target_store
    ),
    all_stores AS (
        SELECT 
            Area,
            Store,
            LISTAGG(Product, ',') WITHIN GROUP (ORDER BY Product) AS product_list,
            COUNT(*) AS product_count
        FROM your_table
        GROUP BY Area, Store
    )
    SELECT COUNT(*) INTO v_match_count
    FROM all_stores
    WHERE 
        product_list = (SELECT product_list FROM target_store)
        AND product_count = (SELECT product_count FROM target_store)
        AND NOT (Area = v_target_area AND Store = v_target_store);
    
    DBMS_OUTPUT.PUT_LINE('匹配的门店数量：' || v_match_count);
END;
/

执行这个块后，会在控制台输出匹配的门店数量。

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