SQL条件查询视图创建求助：关联Item与Service/Product/Package表

正确的SELECT/视图语句 #

用左连接关联三张表，结合COALESCE或CASE就能得到你要的结果，不会产生冗余行：

写法一（推荐，更简洁） #

CREATE VIEW item_details AS
SELECT
    i.id AS "Item ID",
    s.id AS "Service ID",
    p.id AS "Product ID",
    pkg.id AS "Package ID",
    COALESCE(s.name, p.name, pkg.name) AS "Name"
FROM Item i
LEFT JOIN Service s ON i.service_id = s.id
LEFT JOIN Product p ON i.product_id = p.id
LEFT JOIN Package pkg ON i.package_id = pkg.id;

写法二（用CASE明确分支） #

CREATE VIEW item_details AS
SELECT
    i.id AS "Item ID",
    i.service_id AS "Service ID",
    i.product_id AS "Product ID",
    i.package_id AS "Package ID",
    CASE
        WHEN i.service_id IS NOT NULL THEN s.name
        WHEN i.product_id IS NOT NULL THEN p.name
        WHEN i.package_id IS NOT NULL THEN pkg.name
    END AS "Name"
FROM Item i
LEFT JOIN Service s ON i.service_id = s.id
LEFT JOIN Product p ON i.product_id = p.id
LEFT JOIN Package pkg ON i.package_id = pkg.id;

为什么之前用CASE会出冗余行？ #

大概率是误用了内连接而非左连接，或者关联条件写错。内连接会过滤掉不匹配的行，反而可能因为多表关联生成多余组合；而左连接会保留Item的每一行，未匹配的关联表列自动为NULL，完全符合你要的输出格式。

表结构优化建议 #

当前Item表用三个可选外键的设计，虽然业务上保证仅一个非空，但存在硬伤：

• 无法通过数据库约束强制校验“仅一个外键非空”，全靠业务逻辑容易出错
• 后续查询、扩展时容易出现连接错误
• 数据维护成本高

推荐两种优化方案：

方案1：类型标记+单外键 #

新增item_type字段（可设为枚举：'SERVICE'/'PRODUCT'/'PACKAGE'），再加一个related_id字段存储关联ID。但这种方式无法用外键直接约束关联关系，需依赖业务逻辑或触发器校验。

方案2：继承式分表 #

创建主Item表，再分别建ServiceItem、ProductItem、PackageItem子表，每个子表对应关联Service/Product/Package。这种方式符合数据库范式，能通过外键严格约束，查询时用UNION ALL合并数据：

SELECT
    i.id AS "Item ID",
    si.service_id AS "Service ID",
    NULL AS "Product ID",
    NULL AS "Package ID",
    s.name AS "Name"
FROM Item i
JOIN ServiceItem si ON i.id = si.item_id
JOIN Service s ON si.service_id = s.id
UNION ALL
SELECT
    i.id AS "Item ID",
    NULL AS "Service ID",
    pi.product_id AS "Product ID",
    NULL AS "Package ID",
    p.name AS "Name"
FROM Item i
JOIN ProductItem pi ON i.id = pi.item_id
JOIN Product p ON pi.product_id = p.id
UNION ALL
SELECT
    i.id AS "Item ID",
    NULL AS "Service ID",
    NULL AS "Product ID",
    pki.package_id AS "Package ID",
    pkg.name AS "Name"
FROM Item i
JOIN PackageItem pki ON i.id = pki.item_id
JOIN Package pkg ON pki.package_id = pkg.id;

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