时间戳

首页 > 解决方案 > 使用联接创建多个表时如何避免“模棱两可”的错误消息

sql - 使用联接创建多个表时如何避免“模棱两可”的错误消息

问题描述

我正在尝试使用以下代码创建客户及其购买的品牌列表。该brands表有品牌名称，并customer_id在customers表中。要链接它们，我必须通过表（连接到表）和表（连接到表）将brand_id和receipt_id链接在一起。 receiptscustomersreceipt_item_details1brands

因此，receipt_item_details1表（具有brand_id然后连接到brands表的列）和新表customer_receipts（由第一个最内部的子查询创建）正在尝试将其链接到receipt_id. 我想customer_id在构建加入这两个表的表时显示该列（原始：receipt_item_details1加入新表：）customer_receipts。

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

“列引用“customer_id”不明确
第 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

标签： sqlpostgresql

解决方案

你的内部子选择

 (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. 您不需要获取两列（例如 of 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

演示：db<>fiddle

推荐阅读