SQL关联查询出现重复行：如何修复含CASE的JOIN语句？

现有数据表 #

表1：product #

执行查询 select * from product;，结果：

IDPK (PK)   PNAME
----------- --------------------
          1 AAA
          2 BBB
          3 CCC

  3 record(s) selected.

表2：productcost #

执行查询 select * from productcost;，结果：

IDPK (FK)   QUANTITY                   PRICE
----------- -------------------- -----------
          1 100                          100
          1 250                          250
          1 500                          500
          2 100                          100
          2 250                          250
          2 500                          500
          3 100                          100
          3 250                          250
          3 500                          500

  9 record(s) selected.

说明：Quantity单位为克，IDPK（FK）关联product表的IDPK字段。

表3：cart #

执行查询 select * from cart;，结果：

IDPK (FK)   QUANTITY             ITEMCOUNT
----------- -------------------- -----------
          1 100                            5
          1 250                            5

  2 record(s) selected.

说明：Quantity单位为克，IDPK（FK）关联product表的IDPK字段。

关联前两张表的正确查询 #

执行以下查询：

select a.idpk, a.pname, b.quantity, b.price from product a inner join productcost b on a.idpk = b.idpk;

得到9条正确结果：

IDPK        PNAME                QUANTITY                   PRICE           
----------- -------------------- -------------------- -----------
          1 AAA                  100                          100
          1 AAA                  250                          250
          1 AAA                  500                          500
          2 BBB                  100                          100
          2 BBB                  250                          250
          2 BBB                  500                          500
          3 CCC                  100                          100
          3 CCC                  250                          250
          3 CCC                  500                          500

  9 record(s) selected.

期望输出结果 #

IDPK        PNAME                QUANTITY             PRICE             ITEMCOUNT
----------- -------------------- -------------------- -----------       -----------
          1 AAA                  100                          100       5
          1 AAA                  250                          250       5
          1 AAA                  500                          500       0
          2 BBB                  100                          100       0
          2 BBB                  250                          250       0
          2 BBB                  500                          500       0
          3 CCC                  100                          100       0
          3 CCC                  250                          250       0
          3 CCC                  500                          500       0

  9 record(s) selected.

错误查询及结果 #

执行以下查询时返回12条重复行（而非期望的9条）：

SELECT X.idpk,
       X.pname,
       X.quantity,
       X.price,
       CASE
         WHEN X.quantity = Y.quantity THEN Y.itemcount
         ELSE 0
       END AS itemcount
FROM (SELECT a.idpk,
             a.pname,
             b.quantity,
             b.price
      FROM product a
        INNER JOIN productcost b ON a.idpk = b.idpk) X
  LEFT OUTER JOIN (SELECT * FROM cart) Y ON X.idpk = Y.idpk;

错误结果：

IDPK        PNAME                QUANTITY             PRICE       ITEMCOUNT
----------- -------------------- -------------------- ----------- -----------
          1 AAA                  100                          100           5 -- 第一次出现
          1 AAA                  500                          500           0
          1 AAA                  250                          250           0
          1 AAA                  100                          100           0 -- 重复行，不应出现
          1 AAA                  500                          500           0
          1 AAA                  250                          250           5
          3 CCC                  100                          100           0
          3 CCC                  250                          250           0
          3 CCC                  500                          500           0
          2 BBB                  100                          100           0
          2 BBB                  250                          250           0
          2 BBB                  500                          500           0

  12 record(s) selected.

问题原因及修复方案 #

问题原因 #

原查询中LEFT OUTER JOIN仅用*X.idpk = Y.idpk*关联，导致productcost中每条ID为1的记录（共3条）都会和cart中ID为1的两条记录分别关联，产生3*2=6条记录，加上其他ID的6条记录，总共生成12条重复行。

修复后的查询 #

需要在关联条件中同时匹配idpk和quantity，确保productcost的每条记录只和cart中对应ID且对应重量的记录关联，无匹配项则显示0。可以用COALESCE简化空值处理：

方案1：保留子查询结构

SELECT 
    X.idpk,
    X.pname,
    X.quantity,
    X.price,
    COALESCE(Y.itemcount, 0) AS itemcount
FROM (
    SELECT a.idpk, a.pname, b.quantity, b.price
    FROM product a
    INNER JOIN productcost b ON a.idpk = b.idpk
) X
LEFT OUTER JOIN cart Y 
    ON X.idpk = Y.idpk 
    AND X.quantity = Y.quantity;

方案2：直接关联三张表（更简洁）

SELECT 
    a.idpk,
    a.pname,
    b.quantity,
    b.price,
    COALESCE(c.itemcount, 0) AS itemcount
FROM product a
INNER JOIN productcost b ON a.idpk = b.idpk
LEFT JOIN cart c 
    ON a.idpk = c.idpk 
    AND b.quantity = c.quantity;

修复后结果 #

执行上述任意查询，都会返回期望的9条记录，正确匹配对应重量的itemcount，无匹配项则显示0。

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