SQL 无效的表别名或列引用 b
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29704904/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Invalid table alias or column reference b
提问by Daniel
What's wrong with this query (running in hive):
这个查询有什么问题（在 hive 中运行）：
SELECT count(*) TotalCount, b.region_code
from XXX a
INNER JOIN YYY b
ON a.uid=b.uid
where a.dt = '2015-04-15'
group by b.region_code order by b.region_code
I think it should be pretty straightforward, but am getting this:
我认为这应该很简单，但我得到了这个：
FAILED: SemanticException [Error 10004]: Line 6:32 Invalid table alias or column reference 'b': (possible column names are: _col0, _col1)
Here is the YYY table:
这是 YYY 表：
hive> desc YYY;
OK
status_code int
uid string
zip_code string
keyword string
region_code bigint
dt timestamp
channel int
and XXX table:
和 XXX 表：
hive> desc XXX;
OK
group_key string
category string
uid string
dt timestamp
回答by Gordon Linoff
Try doing this:
尝试这样做：
SELECT count(*) as TotalCount, b.region_code
from XXX a INNER JOIN
YYY b
ON a.ui = b.uid
where a.dt = '2015-04-15'
group by b.region_code
order by region_code
The problem with your code is that b.region_codedoesn't exist after the order by. The aliasexists (region_code) because that is in the select. The qualified aliasdoes not, because the bis no longer valid after the group by. I think you could write:
您的代码的问题是b.region_code在order by. 该别名存在的（region_code），因为这是在select。在合格的别名没有，因为b是后不再有效group by。我想你可以写：
order by max(b.region_code)
But that would be silly in this case.
但在这种情况下，这将是愚蠢的。
Note that this is common to all databases, except MySQL.
请注意，这对于除 MySQL 之外的所有数据库都是通用的。
