sql - 使用join创建多张表时如何避免 "ambiguous"错误信息

Question

我正在尝试使用以下代码创建一个客户列表以及他们购买的品牌。 brands 表包含品牌名称，customer_id 在 customers 表中。要链接它们，我必须通过 receipts 表(连接到 customers 表)将 brand_id 和 receipt_id 链接在一起) 和 receipt_item_details1 表(连接到 brands 表)。

因此，receipt_item_details1 表(包含 brand_id 列，然后连接到 brands 表)和新表 customer_receipts (由最内部的第一个子查询创建)正在尝试通过 receipt_id 链接它。我想在构建连接这两个表的表时显示 customer_id 列(原始表:receipt_item_details1 连接到新表:customer_receipts).

问题:我不断收到以下错误。如何中缀它并列出品牌？

"column reference "customer_id" is ambiguous
LINE 3: ...pts.receipt_id, receipt_item_details1.receipt_id, customer_r.."

SELECT customer_brandids.brand_id, brands.brand_id, customer_brandids.customer_id, brands.brand_name
    FROM 
        (SELECT customer_receipts.receipt_id, receipt_item_details1.receipt_id, customer_receipts.customer_id, receipt_item_details1.brand_id
        FROM
            (SELECT receipts.customer_id, customers.customer_id, receipts.receipt_id
            FROM receipts
            INNER JOIN customers
            ON receipts.customer_id = customers.customer_id) AS customer_receipts
        INNER JOIN receipt_item_details1
        ON customer_receipts.receipt_id = receipt_item_details1.receipt_id) AS customer_brandids
    INNER JOIN brands
    ON customer_brandids.brand_id = brands.brand_id

Answer 1

你的内部子选择

 (SELECT receipts.customer_id, customers.customer_id

生成包含名为 customer_id 的两列的结果。因此，如果您引用 customer_id

，则您的下一个更高子选择在两列之间不能不同

你应该给一个或两个一个别名:

 (SELECT receipts.customer_id as r_customer_id, 
      customers.customer_id as c_customer_id

然后你的下一个更高的查询可以调用

 SELECT customer_receipts.c_customer_id...

那么解决问题的第一步:

SELECT 
    customer_brandids.brand_id,                       
    brands.brand_id, 
    customer_brandids.c_customer_id,                    --> reference alias
    brands.brand_name
FROM 
    (SELECT 
         customer_receipts.receipt_id as c_receipt_id,  --> same problem
         receipt_item_details1.receipt_id as r_receipt_id,
         customer_receipts.c_customer_id,               --> reference alias
         receipt_item_details1.brand_id
    FROM
        (SELECT 
             receipts.customer_id as r_customer_id,     --> here was the problem
             customers.customer_id as c_customer_id, 
             receipts.receipt_id
        FROM receipts
        INNER JOIN customers
        ON receipts.customer_id = customers.customer_id) AS customer_receipts
    INNER JOIN receipt_item_details1
    ON customer_receipts.receipt_id = receipt_item_details1.receipt_id) AS customer_brandids
INNER JOIN brands
ON customer_brandids.brand_id = brands.brand_id

---

另外:

1. 您不需要获取两列(例如 receipt_id)，因为 INNER JOIN 可以确保两列具有相同的值
2. 您可以使用别名来缩短您的查询。
3. 您不需要为每个连接创建子查询。加入吧。

总而言之，这应该做同样的事情:

SELECT b.brand_id, c.customer_id, b.brand_name 
FROM receipts r
INNER JOIN customers c ON r.customer_id = c.customer_id
INNER JOIN receipt_item_details1 rid ON r.receipt_id = rid.receipt_id
INNER JOIN brands b ON b.brand_id = rid.receipt_id

demo: db<>fiddle