在WHERE子句中搜索时，PostgreSQL：列不存在(PostgreSQL: column does not exist when searched in WHERE clause)

在WHERE子句中搜索时，PostgreSQL：列不存在(PostgreSQL: column does not exist when searched in WHERE clause)

所以，我有这个小SQL查询：

SELECT COUNT( distinct (customerid)) AS cs, prod_id FROM (orderlines JOIN orders ON (orderlines.orderid=orders.orderid)) AS table_1 WHERE table_1.cs= 1 GROUP BY table_1.prod_id ORDER BY cs ASC

这应该做的是计算不同的customerid并返回一个只包含只有不同customerid的条目的表。

当我执行此操作时，我收到以下错误：

ERROR: column table_1.cs does not exist LINE 6: WHERE table_1.cs= 1 ^ *********Error********** ERROR: column table_1.cs does not exist SQL state: 42703 Character: 156

它声称当我在这里明确定义时，列cs不存在：

SELECT COUNT( distinct (customerid)) AS cs, prod_id

So, I have this little SQL query:

SELECT COUNT( distinct (customerid)) AS cs, prod_id FROM (orderlines JOIN orders ON (orderlines.orderid=orders.orderid)) AS table_1 WHERE table_1.cs= 1 GROUP BY table_1.prod_id ORDER BY cs ASC

What this is supposed to do, is count the distinct customerid's and return a table containing only the entries where there was only distinct customerid.

When I execute this I get the following error:

ERROR: column table_1.cs does not exist LINE 6: WHERE table_1.cs= 1 ^ *********Error********** ERROR: column table_1.cs does not exist SQL state: 42703 Character: 156

It claims that the column cs does not exist when I have clearly defined it here:

SELECT COUNT( distinct (customerid)) AS cs, prod_id

最满意答案

您不能在WHERE子句中引用选择列表中定义的别名。 您可以使用HAVING子句或使用子选择..

使用子选择，您可以安排以下内容：

SELECT * FROM ( SELECT COUNT( distinct (customerid)) AS cs, prod_id FROM (orderlines JOIN orders ON (orderlines.orderid=orders.orderid)) GROUP BY table_1.prod_id) AS table_1 WHERE cs= 1 ORDER BY cs ASC

You can't reference in a WHERE clause an alias defined in the Selection list. You can either use an HAVING clause or use a sub select ..

With a sub select you can arrange something like this :

SELECT * FROM ( SELECT COUNT( distinct (customerid)) AS cs, prod_id FROM (orderlines JOIN orders ON (orderlines.orderid=orders.orderid)) GROUP BY table_1.prod_id) AS table_1 WHERE cs= 1 ORDER BY cs ASC