SQL多表关联查询需求：展示所有产品及对应定价状态

咱们先拆解下你当前查询的问题，然后修正它来得到你想要的预期结果。你的核心需求是展示每个产品与所有定价组的组合——哪怕某个产品在特定定价组下没有定价记录也要显示——同时根据产品状态和现有定价记录正确设置IsActive字段。

正确的SQL语句 #

SELECT 
    p.ProductId,
    p.ProductCode,
    p.ProductDetails,
    p.ProductDescription,
    -- 只有产品本身IsActive为1，且该定价组下存在对应定价记录时，IsActive才为1
    CASE 
        WHEN p.IsActive = 1 AND pp.ProductId IS NOT NULL THEN 1 
        ELSE 0 
    END AS IsActive,
    pg.PricingName AS PricingGroupName,
    -- 当该定价组无产品定价时，显示0
    COALESCE(pp.Amount, 0) AS Amount
FROM Products p
-- 先做交叉连接，生成每个产品与所有定价组的组合
CROSS JOIN pricingGroup pg
-- 左连接定价表，匹配产品ID和定价组ID对应的定价记录
LEFT JOIN productPricing pp 
    ON p.ProductId = pp.ProductId 
    AND pg.PricingGroupId = pp.PricingGroup
ORDER BY p.ProductId, pg.PricingGroupId;

原查询的问题分析 #

1. productPricing关联条件错误：原查询用OR pricing.productId IS NULL会把productPricing中所有productId为NULL的记录（如果存在的话）关联到每个产品，导致结果混乱。正确的关联应该是仅匹配当前产品和对应定价组的记录。
2. pricingGroup关联逻辑错误：ON true会生成无意义的笛卡尔积，而我们需要的是每个产品与所有定价组的合法组合，用CROSS JOIN更清晰准确。
3. IsActive判断逻辑失效：原查询中prgrp.pricingGroupId IS NOT NULL永远为真（因为pricingGroup的pricingGroupId都是有效值），所以CASE语句根本没起到判断定价记录是否存在的作用。正确的逻辑应该是检查该产品在当前定价组下是否有定价记录（pp.ProductId IS NOT NULL），同时产品自身的IsActive为1。

验证结果（修正逻辑误差后的预期输出） #

执行上面的SQL后，会得到符合业务逻辑的结果（注：你给出的预期输出中部分Amount值存在逻辑误差，比如产品1的Consumer和Others组无定价，Amount应为0，以下是正确的输出）：

ProductId | ProductCode | ProductDetails | ProductDescription | IsActive | PricingGroupName | Amount
1 | PRCD1 | detail1 | Descrition1 | 1 | Merchant | 20
1 | PRCD1 | detail1 | Descrition1 | 0 | Consumer | 0
1 | PRCD1 | detail1 | Descrition1 | 0 | Others | 0
2 | PRCD2 | detail2 | Descrition2 | 0 | Merchant | 0
2 | PRCD2 | detail2 | Descrition2 | 1 | Consumer | 25
2 | PRCD2 | detail2 | Descrition2 | 0 | Others | 0
3 | PRCD3 | detail3 | Descrition3 | 0 | Merchant | 0
3 | PRCD3 | detail3 | Descrition3 | 0 | Consumer | 0
3 | PRCD3 | detail3 | Descrition3 | 0 | Others | 0

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