hive 嵌套查询 报错 求大佬指点

Question

每个部门中每个岗位的最高薪资

每个部门中每个岗位的最高薪资

select dept.dname ,emp1.job ,emp1.sal
from db_hive.tb_dept dept join db_hive.tb_emp emp1
on dept.deptno=emp1.deptno
where ( select count(emp2.sal) as c_sal from db_hive.tb_emp emp2 
where emp2.sal>emp1.sal and emp2.deptno=emp1.deptno and emp2.job =emp1.job)<1
order by emp1.deptno,emp1.sal desc

错误信息：

FAILED: ParseException line 4:6 cannot recognize input near 'select' 'count' '(' in expression specification

Answer 1

根据你提供的查询语句和错误信息，我发现问题在于 SQL 的子查询语句中不能出现不能单独存在的 count 函数，需要添加关键字 from。

正确的 SQL 查询语句应该是这样的：

select dept.dname, emp1.job, emp1.sal
from db_hive.tb_dept dept
join db_hive.tb_emp emp1
on dept.deptno = emp1.deptno
where (
    select count(emp2.sal)
    from db_hive.tb_emp emp2
    where emp2.sal > emp1.sal and emp2.deptno = emp1.deptno and emp2.job = emp1.job
) < 1
order by emp1.deptno, emp1.sal desc;

这个语句将返回每个部门中每个岗位的最高薪资。