不明确的列名错误：SQL 计数、连接、分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16599077/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Ambiguous column name error : SQL count, join, group by
提问by Claudia Sasi
I have two tables as below, one table has number of available units (stock), I'm trying to return the stock count of each product category, and joining it with secondary table to view the description and price etc.
我有两个表如下，一个表有可用单位数（库存），我试图返回每个产品类别的库存数量，并将其与辅助表连接以查看描述和价格等。
When I run the below query, I get "Ambiguous column name 'productID'."
当我运行以下查询时，我得到“不明确的列名称‘productID’。”
What am I doing wrong?
我究竟做错了什么？
SQL Query:
SQL查询：
select productID, count (stock)as available_count
from product_units
join product_type ON product_type.description = product_units.productID
group by productID
This returns an error:
这将返回一个错误：
Ambiguous column name 'productID'.
不明确的列名称“productID”。
Table product_type
表product_type
productID description price
101 tent 20.00
102 xltent 50.00
Table product_units
表 product_units
unitID productID stock
1 101 1
2 101 1
3 101 1
4 102 1
Orginal SQL query to get stock count, which works:
用于获取库存数量的原始 SQL 查询，该查询有效：
select productID, count (stock)as available_count
from product_units
group by productID
I'm using SQL Server 2008 R2 with Coldfusion
我正在使用带有 Coldfusion 的 SQL Server 2008 R2
回答by Gordon Linoff
I think your error is more likely "Ambiguous column name 'productID'." And, I'm guessing the join should be on that field as well:
我认为您的错误更有可能是“不明确的列名称‘productID’。” 而且，我猜联接也应该在该领域：
select product_units.productID, count (stock)as available_count
from product_units
join product_type ON product_type.productID = product_units.productID
group by product_units.productID
To select all rows from the product_type table use a right outer join:
要从 product_type 表中选择所有行，请使用right outer join：
select product_units.productID, count (stock)as available_count
from product_units
right outer join product_type ON product_type.productID = product_units.productID
group by product_units.productID
To select all information from the product type table, do the aggregation first and then join:
要从产品类型表中选择所有信息，请先进行聚合然后加入：
select pt.*, pu.available_count
from (select productId, count(stock) as available_count
from product_units
group by productId
) pu join
product_type pt
on pt.productID = pu.productId;
