Postgresql按类型条件求和：可配置产品统计关联子产品总库存查询

你可以在CONFIGURABLE分支用关联子查询统计所有子产品的库存总和，补全后的SQL如下：

SELECT 
pr.id AS product_id,
CASE
    WHEN pr.type = 'SIMPLE' THEN ps.quantity
    WHEN pr.type = 'CONFIGURABLE' THEN (
        SELECT COALESCE(SUM(ps_child.quantity), 0)
        FROM public."product" pr_child
        LEFT JOIN public."product_source" ps_child 
        ON ps_child.product_id = pr_child.id
        WHERE pr_child.configurable_product_id = pr.id
    )
END AS quantity
FROM public."product" as pr
LEFT JOIN public."product_source" as ps
ON ps.product_id = pr.id
ORDER BY pr.id

逻辑说明 #

• SIMPLE类型产品直接取关联的product_source表的自身库存值
• CONFIGURABLE类型产品通过子查询，匹配所有configurable_product_id等于当前产品ID的子产品，汇总这些子产品的库存总和，COALESCE函数用来处理没有子产品时返回0而非null的情况

如果数据量较大，子查询的方式性能一般，可以改用CTE预计算所有SIMPLE产品库存再关联汇总，写法如下：

WITH simple_stock AS (
    SELECT 
        pr.id AS simple_id,
        pr.configurable_product_id,
        ps.quantity
    FROM public."product" pr
    LEFT JOIN public."product_source" ps ON pr.id = ps.product_id
    WHERE pr.type = 'SIMPLE'
)
SELECT 
    pr.id AS product_id,
    CASE
        WHEN pr.type = 'SIMPLE' THEN ss.quantity
        WHEN pr.type = 'CONFIGURABLE' THEN COALESCE(SUM(ss.quantity), 0)
    END AS quantity
FROM public."product" pr
LEFT JOIN simple_stock ss 
    ON (pr.type = 'SIMPLE' AND pr.id = ss.simple_id) 
    OR (pr.type = 'CONFIGURABLE' AND ss.configurable_product_id = pr.id)
GROUP BY pr.id, pr.type, ss.quantity
ORDER BY pr.id

两种写法都可以得到你预期的查询结果。

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