时间戳

首页 > 解决方案 > 查找受监管人数最多的 manager_id

sql - 查找受监管人数最多的 manager_id

问题描述

我无法在 下显示最多的人manager_id。答案是manager_id = 100，但我似乎无法制作一个显示它的 sql。以下是创建并提供给我的 2 个表。

CREATE TABLE departments
    ( department_id    NUMBER(4)
    , department_name  VARCHAR2(30)
    CONSTRAINT  dept_name_nn  NOT NULL
    , manager_id       NUMBER(6)
    , location_id      NUMBER(4)
    ) ;

CREATE TABLE employees
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
     CONSTRAINT     emp_last_name_nn  NOT NULL
    , email          VARCHAR2(25)
    CONSTRAINT     emp_email_nn  NOT NULL
    , phone_number   VARCHAR2(20)
    , hire_date      DATE
    CONSTRAINT     emp_hire_date_nn  NOT NULL
    , job_id         VARCHAR2(10)
    CONSTRAINT     emp_job_nn  NOT NULL
    , salary         NUMBER(8,2)
    , commission_pct NUMBER(2,2)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
    , CONSTRAINT     emp_salary_min
                     CHECK (salary > 0) 
    , CONSTRAINT     emp_email_uk
                     UNIQUE (email)
    ) ;

下面是我的代码，我试图将这两个表连接起来，employees并在它们之间departments找到manager_id出现次数最多的表。

每次我尝试运行我的 sql 块时，它都会给我一个错误，"ORA-00918: column ambiguously defined"或者有什么问题Limit 1

SELECT COUNT(Manager_id) into v_manager_id, 
FROM departments d
RIGHT JOIN employees e
ON d.manager_id = e.manager_id
GROUP BY Manager_id
ORDER BY COUNT(Manager_id) DESC
LIMIT 1;

标签： sqloracleoracle11g

解决方案

这将起作用：

select manager_id 
from (select manager_id,count(*)  
      from employees  
      group by manager_id 
      order by 
      count(*) desc)
where rownum<=1 ;

您还可以像这样使用嵌套子查询：

create table ns_231(col1 number,col2 number);

insert into ns_231 values(1,1);
insert into ns_231 values(2,3);
insert into ns_231 values(3,3);
insert into ns_231 values(1,2);
insert into ns_231 values(2,5);
insert into ns_231 values(2,1);
insert into ns_231 values(3,1);
insert into ns_231 values(1,4);

SELECT * FROM ns_231;
commit;
select col1 from (select col1,count(*) from ns_231  group by col1 order by count(*) desc) where rownum<=1 ;

select col1 from ns_231 group by col1
having count(*)=(select max(total) from (select count(*) as total from 
ns_231 group by col1));

输出：

1
2

对于您的表，查询是：

select manager_id from employees group by manager_id
having count(*)=(select max(total) from (select count(*) as total from 
employees group by manager_id));

推荐阅读