mysql - SQL 查询不适用于连接

Question

首先，小查询可以工作，但我需要在其中放置一些连接来创建条件。小查询如下:

SELECT COUNT(vis.id), country.cod
FROM visits AS vis, countries AS country
WHERE country.description = vis.country
GROUP BY country.cod

现在的问题是我需要在 where 子句中添加另一个条件，而这个条件必然来自两个表的联接。

SELECT COUNT(vis.id), country.cod
FROM visits AS vis, countries AS country
INNER JOIN products AS prod ON prod.id = vis.id_product
INNER JOIN customers AS cust ON cust.id = prod.id_customer
WHERE country.description = visit.country AND prod.id_customer = 13
GROUP BY country.cod

我收到的错误是:

#1054 - Unknown column 'vis.id_product' in 'on clause'

相信我，这个领域是存在的。另外，我尝试过，只是为了测试，放置其他字段而不是 id_product ，错误是相同的。

Answer 1

您已为表visits指定了别名vis。现在你需要使用它。

SELECT COUNT(vis.id), country.cod
FROM visits AS vis JOIN
     countries AS country
     ON  country.description = vis.country JOIN
     products AS prod
     ON prod.id = vis.id_product JOIN
     customers AS cust
     ON cust.id = prod.id_customer
WHERE prod.id_customer = 13
GROUP BY country.cod;

此外，您还混合了隐式连接和显式连接。仅使用显式连接，其中连接条件位于 on 子句中，而不是位于 where 子句中。一个简单的规则:切勿在 from 子句中使用逗号。