mysql - 左加入。字段列表中的列 'product_id' 不明确

Question

SELECT product_id,time_id,customer_id,promotion_id,store_id,store_sales,store_cost,SUM(unit_sales) total_unit_sales

FROM sales_fact_1997 a
LEFT JOIN product p ON (a.product_id = p.product_id)

group by product_id order by total_unit_sales;

我想知道为什么我收到错误:

字段列表中的“product_id”列不明确

我引用的两个表都有一个 Product_id 列。

提前致谢...

Answer 1

Mysql 不知道你想要哪个product_id。您需要在选择中的“product_id”列之前使用别名。即(假设您想要 sales_fact_1997 形式的product_id)，

SELECT a.product_id,time_id,customer_id,promotion_id,store_id,store_sales,store_cost,SUM(unit_sales) total_unit_sales

FROM sales_fact_1997 a
LEFT JOIN product p ON (a.product_id = p.product_id)

group by a.product_id order by total_unit_sales;