问题描述
SELECT
cat.CategoryID as CategoryID,
count(p.ProductID) as CountProducts
FROM
Category as cat
LEFT JOIN Products as p on p.CategoryID IN
(SELECT CategoryID FROM Category as cd
WHERE cd.ParrentCategoryID = '876')
WHERE
CategoryID = '876'
ORDER by Name
我们遇到错误-"where子句中的列'CategoryID'不明确".
We are get error - "Column 'CategoryID' in where clause is ambiguous".
告诉我好吗?
推荐答案
您收到的错误告诉您WHERE
子句中的CategoryID
列是不明确的,这意味着系统在识别适当的位置时存在问题列,因为有多个CategoryID
列.
The error you get tells you that the column CategoryID
in your WHERE
clause is ambiguous, that means that the system has a problem to identify the appropriate column because there are multiple CategoryID
columns.
为了解决此问题,请使用别名指定要用于WHERE
子句的列:
In order to fix this problem, use the alias to specify which column you want to use for your WHERE
clause:
SELECT cat2.CategoryID AS CategoryID
,cat2.Name AS CategoryName
,COUNT(p.ProductID) AS CountProducts
FROM Category AS cat
INNER JOIN Category AS cat2 ON cat2.ParrentCategoryID = cat.CategoryID
INNER JOIN Products AS p ON p.CategoryID = cat2.CategoryID
WHERE cat.CategoryID = '876'
GROUP BY cat2.CategoryID, cat2.Name
ORDER BY cat2.Name
我还对查询进行了一些更改,以获得相同的结果,但是我没有使用LEFT JOIN
+ IN
子句+子查询的组合,而是使用了INNER JOIN
子句.使用此查询,您只需定义一次所需的CategoryID
,它将自动获取每个子类别.
I also changed a bit the query to get the same result but instead of using a combination of LEFT JOIN
+ IN
clause + sub query, i used INNER JOIN
clauses.With this query you only need to define your desired CategoryID
once and it will automatically get every child categories.
我不确定您的查询是否正确运行,因为您使用的是COUNT
函数,而未按CategoryID
...
I'm not sure that your query runs correctly because you're using the COUNT
function without grouping the results by CategoryID
...
希望这会对您有所帮助.
Hope this will help you.
这篇关于mysql ...在where子句不明确的地方的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!