我正在尝试获得一个计数的最大值。代码如下

SELECT coachID, COUNT(coachID)
FROM coaches_awards GROUP BY coachID
HAVING COUNT(coachID) =
(
   SELECT MAX(t2.awards)
    FROM (
             SELECT coachID, count(coachID) as awards
             FROM coaches_awards
             GROUP BY coachID
          ) t2
);

然而,某些事情一直在失败。内部查询工作并给出我想要的答案,如果内部查询被所需的数字替换,外部查询将工作。所以我假设我犯了一些语法错误。

我要去哪里错了?

最佳答案

如果您只想查找一行,为什么不这样做:

SELECT coachID, COUNT(coachID) as cnt
FROM coaches_awards
GROUP BY coachID
ORDER BY cnt DESC
LIMIT 1;

如果您想要联系,请使用RANK()DENSE_RANK():
SELECT ca.*
FROM (SELECT coachID, COUNT(*) as cnt,
             RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM coaches_awards
      GROUP BY coachID
     ) ca
WHERE seqnum = 1;

关于hadoop - HiveSQLException:无法识别表达式规范中 'SELECT' 'MAX' '('附近的输入,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43990489/

10-11 08:44