sql - 专栏不存在？

Question

想知道是否有人可以帮助我解决这个问题:

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 的结果。此查询出现错误:

 ERROR:  column "tempcolumn" does not exist

但是列应该存在，对吗？有人可以帮忙吗？谢谢!

Answer 1

您不能在 WHERE 子句中引用列别名。

  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 子句中访问。