在 Hiveql 中，SemanticException [错误 10004]：第 1：126 行 无效的表别名或列引用"时间表"：(可能的列名称为：作者)

Question

我的查询遵循这个

select drivers.Author 
from timesheet 
join drivers 
on drivers.BibNum = timesheet.BibNum
group by drivers.Author 
order by count(timesheet.BibNum) desc
limit 1;

但是我得到了这个错误，

编译语句时出错：失败：语义异常 [错误 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 |
+-----------------+-----------------+-----------------+--+

如果你知道原因，请开导我，谢谢你的帮助。 如果您需要有关我的架构的更多信息，请告诉我。

Answer 1

按 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;

Answer 2

错误与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