PostgreSQL：如何依据同家族产品状态设置主产品product_launch字段

可以通过**CTE（通用表表达式）**先计算每个家族是否存在已发布（product_launch = true）的产品，再通过UNION ALL合并三类需求数据，具体SQL实现如下：

WITH family_launch_status AS (
    -- 计算每个家族是否有任意产品已发布
    SELECT 
        family_id,
        MAX(CASE WHEN product_launch THEN 1 ELSE 0 END) = 1 AS has_any_launched
    FROM products
    WHERE family_id IS NOT NULL
    GROUP BY family_id
)
-- 1. 无家族的所有产品
SELECT 
    product_id,
    product_name,
    product_launch,
    family_id,
    is_main
FROM products
WHERE family_id IS NULL

UNION ALL

-- 2. 有家族且非主产品的所有产品
SELECT 
    product_id,
    product_name,
    product_launch,
    family_id,
    is_main
FROM products
WHERE family_id IS NOT NULL 
  AND is_main = false

UNION ALL

-- 3. 有家族的主产品（同步家族内的发布状态）
SELECT 
    p.product_id,
    p.product_name,
    -- 若家族内有已发布产品，则主产品的发布状态设为true，否则保留原状态
    CASE WHEN f.has_any_launched THEN true ELSE p.product_launch END AS product_launch,
    p.family_id,
    p.is_main
FROM products p
JOIN family_launch_status f 
    ON p.family_id = f.family_id
WHERE p.is_main = true;

代码说明： #

1. CTE family_launch_status：通过GROUP BY family_id聚合每个家族的产品，用MAX(CASE...)判断家族内是否存在任意产品的product_launch为true，返回每个家族的has_any_launched标记。
2. 第一部分查询：直接筛选无家族（family_id IS NULL）的产品，保留原始字段。
3. 第二部分查询：筛选有家族且非主产品（is_main = false）的产品，保留原始字段。
4. 第三部分查询：关联CTE获取家族的发布状态，通过CASE语句覆盖主产品的product_launch字段——只要家族内有已发布产品，主产品的该字段就设为true，否则保留原值。

这样就能满足所有需求：无家族产品、非主产品正常返回，主产品同步家族内的发布状态，且同一家族仅返回主产品。

内容的提问来源于stack exchange，提问作者allan.egidio