MySQL中where子句中的in指令含义不明确

Question

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’列是模糊的”。请告诉我正确的方法。

- user2881809

---

CategoryID 在两个表中都存在。使用表的名称来命名标识符列并不是一个好习惯——id 是行业标准，因为它效果最佳。 - Bohemian

在“ON”子句中使用“IN(subquery)”谓词非常奇怪。通常，“ON”子句中的谓词会引用“FROM”子句中表的列。(我很难理解所需的结果集是什么。) - spencer7593

Answer 1

您收到的错误提示表明，在您的WHERE子句中，列CategoryID是有歧义的，这意味着系统无法确定适当的列，因为存在多个CategoryID列。

为了解决这个问题，请使用别名指定您希望在WHERE子句中使用哪个列：

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，它将自动获取每个子类别。

我不确定你的查询语句是否正确，因为你在没有按 CategoryID 分组的情况下使用了 COUNT 函数...

希望这能对你有所帮助。

- Joël Salamin

---

+1. 这是查询的更常见模式；ON子句中的谓词引用FROM子句中表的列（与OP查询中的IN(subquery)不同）。 - spencer7593

哦，对不起，这是因为我熬夜太久了，没能发现这个小错误。非常感谢您的帮助和新知识。 - user2881809

@learner 如果你从中学到了一些东西，那就很开心，小错误是开发者生活的一部分；-) 不要忘记标记一个答案为“已采纳”，以关闭问题。 - Joël Salamin

Answer 2

“模糊列”错误意味着存在对列标识符的引用，并且MySQL有两个（或多个）与规范匹配的可能列。

在这种特定情况下，它是对“WHERE”子句中CategoryID列的引用。

有点难以确定CategoryID是指来自cat的CategoryID列，还是来自p的CategoryID列。

修复方法是使用表别名限定列引用以消除歧义。也就是说，用cat.CategoryID或p.CategoryID代替CategoryID。

一些额外的注释：

在ON子句中具有IN(subquery)谓词非常奇怪。如果CategoryID在Category表中不能保证唯一，则此查询可能会生成比预期更多的行。

像这样的查询的常规模式会类似于：

SELECT cat.CategoryID     AS CategoryID
     , COUNT(p.ProductID) AS CountProducts
  FROM Category cat
  LEFT 
  JOIN Products p
    ON p.CategoryID = cat.CategoryID 
 WHERE cat.ParrentCategoryID = '876'
 ORDER BY cat.Name

通常，在ON子句中的连接谓词引用FROM子句中两个（或更多）表中的列。（这不是SQL要求，这只是通常的模式。）

上面查询中ON子句中的谓词指定了cat（Category）表的CategoryID列的值与p（Product）表中的CategoryID列的值匹配。

此外，最佳实践是使用表名或表别名限定查询中所有引用的列，即使列名不含歧义。例如，ORDER BY子句中的Name列。

这样做的一个重要好处是，如果将来在Products表中添加了一个名为Name的列，则对Name的非限定引用的查询会开始抛出“歧义列”错误。因此，限定所有列名可以避免新列添加到现有表时破坏工作查询。

另一个重要好处是对语句的读者。MySQL可以快速查找字典，找到Name所在的表。但是像我这样不知道哪个表包含名为Name的列的SQL语句读者也需要查看表定义才能找到。如果名称有限定符，我们就已经知道该列来自哪个表。

- spencer7593

---

抱歉问题不好，昨晚熬夜了... 感谢帮助，加一分。 - user2881809

非常欣赏这种简单明了的表述，即使用table.column语法来指定表格。 - Sarah Diri

Answer 3

尝试使用WHERE cat.CategoryID = '876'。

- bloomingsmilez