在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_idSo, 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 ASCWhat 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: 156It 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 ASCYou 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更多推荐
发布评论