mysql ...在where子句不明确的地方 [英] mysql ...in where clause is ambiguous

问题描述

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子句不明确的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！