Postgres中如何按不同外键动态实现表的交叉连接？

明确结论 #

该需求可以直接在PostgreSQL内实现，无需在服务端编程语言中处理。

注：原手动实现示例中引用的merchant_option为笔误，实际表名应为题目给出的merchant_item，以下方案均基于正式表结构开发。

方案1：递归CTE实现（纯静态SQL，无需存储过程） #

该方案将所有组合以商品ID数组/商品名称数组的格式返回，适合后续对组合做评分、排序的场景，入参$1为目标用户ID：

WITH RECURSIVE customer_merchants AS (
    -- 给用户收藏的商户按顺序生成编号
    SELECT 
        merchant_id,
        ROW_NUMBER() OVER (ORDER BY merchant_id) AS rn,
        COUNT(*) OVER () AS total_merchant_count
    FROM favorite_merchant
    WHERE customer_id = $1
),
cross_join_result AS (
    -- 递归初始层：取第一个商户的所有商品作为初始组合
    SELECT 
        ARRAY[item_id] AS item_id_combo,
        ARRAY[item_name] AS item_name_combo,
        1 AS current_rn
    FROM merchant_item mi
    JOIN customer_merchants cm ON mi.merchant_id = cm.merchant_id
    WHERE cm.rn = 1
    UNION ALL
    -- 递归层：每一层和下一个商户的所有商品做交叉连接
    SELECT 
        cjr.item_id_combo || mi.item_id,
        cjr.item_name_combo || mi.item_name,
        cjr.current_rn + 1
    FROM cross_join_result cjr
    JOIN customer_merchants cm ON cm.rn = cjr.current_rn + 1
    JOIN merchant_item mi ON mi.merchant_id = cm.merchant_id
)
-- 取最终完成所有商户交叉的组合
SELECT item_id_combo, item_name_combo
FROM cross_join_result
WHERE current_rn = (SELECT total_merchant_count FROM customer_merchants LIMIT 1);

效果验证 #

• 入参$1=1（用户收藏商户1、2）：返回2*2=4条组合
• 入参$1=2（用户收藏商户1、2、3）：返回222=8条组合
• 入参$1=3（用户仅收藏商户3）：返回2条单商品组合

方案2：PL/pgSQL动态SQL实现（返回多列结构化结果） #

如果需要返回和商户数量对应的多列结果（而不是数组），可以通过PL/pgSQL函数动态拼接交叉连接语句实现：

CREATE OR REPLACE FUNCTION get_customer_item_combos(p_customer_id INT)
RETURNS TABLE (combo_id INT, combo_items jsonb) AS $$
DECLARE
    v_merchant_ids INT[];
    v_sql TEXT;
    v_select_cols TEXT;
    v_join_clause TEXT;
BEGIN
    -- 获取用户收藏的所有商户ID
    SELECT ARRAY_AGG(merchant_id) INTO v_merchant_ids
    FROM favorite_merchant
    WHERE customer_id = p_customer_id;

    -- 拼接动态SQL
    v_select_cols := STRING_AGG('m'||idx||'.item_name AS merchant_'||merchant_id||'_item', ', ')
    FROM UNNEST(v_merchant_ids) WITH ORDINALITY AS t(merchant_id, idx);

    v_join_clause := STRING_AGG('CROSS JOIN merchant_item m'||idx||' WHERE m'||idx||'.merchant_id = '||merchant_id, ' ')
    FROM UNNEST(v_merchant_ids) WITH ORDINALITY AS t(merchant_id, idx);

    v_sql := 'SELECT ROW_NUMBER() OVER() AS combo_id, to_jsonb(t.*) AS combo_items FROM (SELECT '||v_select_cols||' FROM merchant_item m1 '||v_join_clause||') t';

    -- 执行动态SQL并返回结果
    RETURN QUERY EXECUTE v_sql;
END;
$$ LANGUAGE plpgsql STABLE;

-- 调用方式
SELECT * FROM get_customer_item_combos(1);

方案选型建议 #

• 如果后续需要对组合做统一的评分计算、排序过滤，优先选递归CTE方案，数组格式处理更便捷
• 如果需要直接输出多列结构化的组合结果，选动态SQL方案即可

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