列不存在? [英] Column doesn't exist?

查看:81
本文介绍了列不存在?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

想知道是否有人可以帮我解决这个查询:

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 允许在 HAVINGGROUP 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆