列不存在? [英] Column doesn't exist?
本文介绍了列不存在?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
想知道是否有人可以帮我解决这个查询:
Was wondering if someone could help me out a little with this query:
SELECT u1.id,count(DISTINCT u2.userstatus) as TEMPCOLUMN FROM users AS u1
JOIN friendssym ON u1.id = friendssym.user_id
JOIN (SELECT * FROM users) as u2 ON friendssym.friend_id=u2.id
WHERE TEMPCOLUMN=1
group by u1.id;
我只想得到计数(已重命名)等于 1 的结果.此查询出现错误:
I want to only have results where the count (which is renamed) is equal to 1. I get an error with this query:
ERROR: column "tempcolumn" does not exist
但是列应该存在,对吗?任何人都可以提供帮助吗?谢谢!
But the column should exist, right? Can anyone assist? Thanks!
推荐答案
不能在 WHERE 子句中引用列别名.
You can't reference a column alias in the WHERE clause.
SELECT u1.id,
COUNT(DISTINCT u2.userstatus) as TEMPCOLUMN
FROM USERS AS u1
JOIN friendssym ON u1.id = friendssym.user_id
JOIN USERS as u2 ON friendssym.friend_id = u2.id
GROUP BY u1.id
HAVING COUNT(DISTINCT u2.userstatus) = 1
在传统 SQL 中,最早可以引用列别名的是 ORDER BY
子句.但是 MySQL 和 SQL Server 允许在 HAVING
和 GROUP BY
子句中访问.
In traditional SQL, the earliest you can reference a column alias is the ORDER BY
clause. But MySQL and SQL Server allow access in the HAVING
and GROUP BY
clauses.
这篇关于列不存在?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文