在Hiveql中，SemanticException [错误10004]：第1行:126个无效表别名或列引用‘时间表’：(可能的列名是:作者)
在Hiveql中，SemanticException [错误10004]：第1行:126个无效表别名或列引用‘时间表’：(可能的列名是:作者)
提问于 2018-04-08 19:11:31
我的查询如下所示
select drivers.Author
from timesheet
join drivers
on drivers.BibNum = timesheet.BibNum
group by drivers.Author
order by count(timesheet.BibNum) desc
limit 1;但我有个错误，
编译语句时出错:失败: SemanticException错误10004:第1行:126个无效表别名或列引用‘时间表’：(可能的列名是:作者)
我的桌子看起来像这样
驱动程序
+-----------------+-----------------+-----------------+--+
| bibnum | string | from deserializer |
| title | string | from deserializer |
| author | string | from deserializer |
| isbn | string | from deserializer |
| publicationyear | string | from deserializer |
| publisher | string | from deserializer |
| subjects | string | from deserializer |
| itemtype | string | from deserializer |
| itemcollection | string | from deserializer |
| floatingitem | string | from deserializer |
| itemlocation | string | from deserializer |
| reportdate | string | from deserializer |
| itemcount | string | from deserializer |
+-----------------+-----------------+-----------------+--+时间表
+-----------------+-----------------+-----------------+--+
| bibnum | string | from deserializer |
| itembarcode | string | from deserializer |
| itemtype | string | from deserializer |
| itemcollection | string | from deserializer |
| callnumber | string | from deserializer |
| checkoutdatetime | string | from deserializer |
+-----------------+-----------------+-----------------+--+如果你知道原因，请指点我，谢谢你的帮助。如果您需要更多的信息到我的架构，请告诉我。
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-04-08 19:20:20
按bibnum加入两个表，然后按aurhor名称分组。最受欢迎的作者会有最多的读者，所以按降序数。极限1将只获得顶部的第一个记录。
编辑:使用HIVE代替mysql
SELECT tab.Author
from (
SELECT t2.Author,
count(t1.Bibnum) as cnt
FROM timesheet t1
INNER JOIN drivers t2 on trim( t1.BibNum) = trim(t2.Bibnum)
WHERE LENGTH(trim(t2.Author)) > 0
GROUP BY t2.Author
ORDER BY cnt DESC
LIMIT 1) tab;Stack Overflow用户
发布于 2018-04-08 19:14:47
错误发生在order by上。只有selected列才能进入其中。更改查询以删除order by。或者将count添加到select并使用它进行排序。
select drivers.Author,count(timesheet.BibNum) as cnt
from timesheet
join drivers on drivers.BibNum = timesheet.BibNum
group by drivers.Author
order by cnt 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49721722
复制相关文章
相似问题
腾讯云开发者
