如何合并两个SQL查询为单个，保留首个查询的全部结果

原有代码问题 #

• 采用隐式内连接（逗号分隔表），会过滤掉第二个查询无匹配的第一个查询结果，不符合保留全部第一个查询结果的需求
• 未做空值处理，无匹配时invoiced字段返回NULL而非要求的0
• 先关联再聚合的逻辑会放大计算量，性能比预聚合后关联差

修正后完整代码 #

WITH line_data AS (
SELECT
    btrim(store."TEXT") AS warehouse, 
    style."EXT_ID" AS style_code,  
    style."TEXT" AS style_desc, 
    roll."COLOR" AS color_code,
    roll."COLOR_TEXT" AS color_desc,
    roll."EXT_ID" as roll_number,
    roll."QTY_UNIT" as unit_measure,
    roll."COST_UNIT_PRICE" as cost,
    round(roll."ROLL_WIDTH",2) AS width,
    round(sum( CASE WHEN roll."EXT_ID" not like 'PND%' THEN roll."QTY_AVAIL" ELSE 0 END),2) as in_store,
    round(sum( CASE WHEN roll."EXT_ID" ~ 'PND-(?!PO)[A-Z]' THEN roll."QTY_AVAIL" ELSE 0 END),2) as on_pnd,
    sku."ITEM_CLASS" AS category,
    supplier."EXT_ID" AS vendor_code,
    supplier."TEXT" AS vendor_name,
    store."EXT_ID" as warehouse_number,
    'GFS' as "system",
    store."REGION_ID" as region
 FROM "ROLL" roll
   LEFT JOIN "STYLE" style ON style."ID"::text = roll."STYLE_ID"::text
   LEFT JOIN "SKU" sku ON sku."ID"::text = roll."SKU"::text
   LEFT JOIN "STORE" store ON store."ID"::text = roll."WHSE_ID"::text
   LEFT JOIN "SUPPLIER" supplier ON supplier."ID"::text = style."MFR"::text
WHERE roll."QTY_AVAIL" > 0.99 and style."EXT_ID" is not null  
GROUP BY store."EXT_ID",store."TEXT",style."EXT_ID",style."TEXT",roll."COLOR",roll."COLOR_TEXT",roll."QTY_UNIT",roll."COST_UNIT_PRICE", roll."EXT_ID", roll."ROLL_WIDTH",sku."ITEM_CLASS",supplier."EXT_ID",supplier."TEXT", system, store."REGION_ID"
),
-- 提前预聚合发票数据，减少关联数据量
invoice_agg AS (
SELECT
    store."EXT_ID" AS warehouse,
    style."EXT_ID" AS style_code,
    invoice_item."COLOR" AS color_code,
    round(sum(CASE WHEN invoice_item."UNIT_COST_UNIT_PRICE"::numeric < 0.00 THEN invoice_item."QTY" * '-1'::numeric ELSE invoice_item."QTY"::numeric END),2)  AS invoiced
FROM "INVOICE_ITEM" invoice_item
  LEFT JOIN "STYLE" style ON style."ID"::text = invoice_item."STYLE_ID"::text
  LEFT JOIN "INVOICE" invoice ON invoice_item."INVOICE_ID"::text = invoice."ID"::text
  LEFT JOIN "STORE" store ON store."ID"::text = invoice."STORE_ID"::text
WHERE invoice_item."CREATED_ON" >= '2021-01-01' and invoice_item."CREATED_ON" <= now()
GROUP BY store."EXT_ID", style."EXT_ID", invoice_item."COLOR"
)

SELECT 
    l_d.*,
    COALESCE(i_a.invoiced, 0) AS invoiced
FROM line_data l_d
LEFT JOIN invoice_agg i_a 
    ON i_a.warehouse = l_d.warehouse
    AND i_a.style_code = l_d.style_code
    AND i_a.color_code = l_d.color_code
ORDER BY l_d.vendor_code, l_d.style_code, l_d.color_code;

优化点说明 #

• 新增invoice_agg CTE提前按关联维度聚合发票数据，避免关联后重复计算，性能提升明显
• 用LEFT JOIN替代原有的隐式内连接，完整保留line_data的所有结果
• 用COALESCE函数将无匹配的invoiced空值转换为0，符合需求
• 最终查询保留原有排序逻辑，结果顺序和第一个查询完全一致

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