如何获取所有产品类型并区分是否分配给指定仓库（含无匹配）

• 仓库表warehouse包含仓库类型字段（type_id，关联warehouse_types表）
• 产品类型表product_types存储所有可在仓库中存在的产品类型，通过关联表products_type_by_wh_types与仓库类型建立关联
• 目标：获取所有产品类型，标记哪些基于仓库类型分配给指定仓库，哪些未分配

当前尝试的SQL查询 #

SELECT DISTINCT w.id, w.name, wht.type AS whouse_type, APT.product_type_id, APT.product_type,
CASE
    WHEN w.type_id = APT.warehouse_type_id
        THEN "YES"
    WHEN w.type_id <> APT.warehouse_type_id
        THEN "NO"
    WHEN APT.warehouse_type_id IS NULL
        THEN "NO"
    ELSE NULL
END AS assigned
FROM warehouse w
LEFT JOIN warehouse_types wht ON w.type_id = wht.id
CROSS JOIN (
    SELECT DISTINCT pt.id AS product_type_id, pt.product_type, rel.warehouse_type_id
    FROM product_types pt 
    LEFT JOIN products_type_by_wh_types rel ON pt.id = rel.product_type_id
  ) AS APT -- all products types 
where w.id = 1
ORDER BY w.name, APT.product_type_id, type_of_warehouse;

当前查询结果 #

+----+-------------+-------+--------------+----------+
| id | whouse_type | pt_id | product_type | assigned |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   1   | Industry     | YES      |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   1   | Industry     | NO       |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   2   | Transport    | NO       |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   3   | Chemicals    | NO       |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   4   | Food and B   | YES      |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   4   | Food and B   | NO       |
+----+-------------+-------+--------------+----------+

预期结果 #

+----+-------------+-------+--------------+----------+
| id | whouse_type | pt_id | product_type | assigned |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   1   | Industry     | YES      |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   2   | Transport    | NO       |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   3   | Chemicals    | NO       |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   4   | Food and B   | YES      |
+----+-------------+-------+--------------+----------+

解决方案 #

问题根源在于CROSS JOIN的子查询返回了同一产品类型的多条记录（对应不同仓库类型），导致CASE判断生成重复行。正确的做法是通过存在性检查来标记状态：

SELECT 
    w.id,
    wht.type AS whouse_type,
    pt.id AS pt_id,
    pt.product_type,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM products_type_by_wh_types rel
            WHERE rel.product_type_id = pt.id 
              AND rel.warehouse_type_id = w.type_id
        ) THEN 'YES'
        ELSE 'NO'
    END AS assigned
FROM warehouse w
JOIN warehouse_types wht ON w.type_id = wht.id
CROSS JOIN product_types pt
WHERE w.id = 1
ORDER BY pt.id;

逻辑说明 #

1. 锁定目标仓库w.id=1，获取其对应的仓库类型w.type_id
2. 通过CROSS JOIN关联所有产品类型，确保每个产品类型都被列出
3. 使用EXISTS子查询检查当前产品类型是否与该仓库类型存在关联记录：存在则标记YES，否则标记NO
4. 每个产品类型仅返回一行，完全匹配预期结果

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