mysql引用表无效列,无效的表别名或列引用b

What's wrong with this query (running in 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:

hive> desc YYY;

OK

status_code int

uid string

zip_code string

keyword string

region_code bigint

dt timestamp

channel int

and XXX table:

hive> desc XXX;

OK

group_key string

category string

uid string

dt timestamp

解决方案

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_code doesn't exist after the order by. The alias exists (region_code) because that is in the select. The qualified alias does not, because the b is no longer valid after the group by. I think you could write:

order by max(b.region_code)

But that would be silly in this case.

Note that this is common to all databases, except MySQL.