使用Oracle的SQL中缺少表达式的问题
使用Oracle的SQL中缺少表达式的问题
提问于 2019-01-06 00:27:55
我想要按部门获取员工数量，我使用Oracle编写了此脚本，但它总是显示缺少一个表达式
我的表中使用的列：
department :name (the name of the department) -
depnum (the id of the department"primary key"),
employee : empnum (the id of the employee) -
depnum (the id of the department in which the employee in question is working "foreign key")查询：
select
s.name
from
department s
inner join
employee p on s.depnum = p.depnum
group by
s.name
having
count(p.empnum) = max(select count(p.empnum)
from employee p, department s
where s.depnum = p.depnum
group by s.name) ;回答 2
Stack Overflow用户
回答已采纳
发布于 2019-01-06 00:34:21
如果您想要按部门划分的员工数量，我希望是这样的：
select s.name, count(*) as num_employees
from department s inner join
employe p
on s.depnum = p.depnum
group by s.name ;如果您希望部门名称具有最大数量的名称，则可以使用having子句：
select s.name, count(*) as num_employees
from department s inner join
employe p
on s.depnum = p.depnum
group by s.name
having count(*) = (select max(cnt)
from (select count(*) as cnt
from employee e2
group by e2.depnum
) e2
);查询的问题是您试图获取一个子查询的max()。这种语法是不允许的--也不是必需的。
Stack Overflow用户
发布于 2019-01-06 00:32:49
您的sql语句不正确，这就是它抛出该错误的原因。我想你尝试过下面这样的东西
select s.name
from department s
inner join employe p on s.depnum=p.depnum
group by s.name
having count(p.empnum)=
select max(cnt) from
(
select count(p.empnum) as cnt
from employe p join department s
on s.depnum=p.depnum
group by s.name
) t;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54053910
复制相关文章
相似问题
腾讯云开发者
