Manager_id Department_id
100 50
124 50
124 50
124 50
124 50
100 80
149 80
149 80
90
100 90
100 90题目是找出所以部门里共同的经理代码,结果应该是
Manager_id
100有人能告诉我怎么编吗? 多谢指教!!
100 50
124 50
124 50
124 50
124 50
100 80
149 80
149 80
90
100 90
100 90题目是找出所以部门里共同的经理代码,结果应该是
Manager_id
100有人能告诉我怎么编吗? 多谢指教!!
where exists (select 1 from tablename b
where a.Manager_id=b.Manager_id
and a.Department_id<>b.Department_id)
--楼上的好像不行?WITH tab AS(
SELECT 100 Manager_id ,50 Department_id FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 100, 80 FROM dual UNION ALL
SELECT 149, 80 FROM dual UNION ALL
SELECT 149, 80 FROM dual UNION ALL
SELECT 100, 90 FROM dual UNION ALL
SELECT 124, 90 FROM dual
)
select Manager_id from (
select Manager_id from (
select distinct Manager_id,Department_id from tab
)
group by Manager_id
order BY Count(1) DESC
)where rownum=1
select Manager_id from (
select Manager_id, count(distinct Department_id) cn
from table group by Manager_id order by cn desc)tb
where rownum < 2
作为管理者应该知道一共有多少部门,至少可以从部门表count出来,假定为Nselect Manager_id from (
select Manager_id, count(distinct Department_id) cn
from table group by Manager_id )tb
where cn = N
这句sql比较巧妙,推荐一下。
from (select Manager_id
from(select distinct Manager_id,Department_id from tb)
group by Manager_id
order by count(*) desc)
where rownum<2
已写入 file afiedt.buf 1 with tb as (
2 SELECT 100 Manager_id ,50 Department_id FROM dual UNION ALL
3 SELECT 124, 50 FROM dual UNION ALL
4 SELECT 124, 50 FROM dual UNION ALL
5 SELECT 124, 50 FROM dual UNION ALL
6 SELECT 124, 50 FROM dual UNION ALL
7 SELECT 100, 80 FROM dual UNION ALL
8 SELECT 149, 80 FROM dual UNION ALL
9 SELECT 149, 80 FROM dual UNION ALL
10 SELECT 100, 90 FROM dual UNION ALL
11 SELECT 124, 90 FROM dual
12 )
13 select distinct Manager_id
14 from tb a
15 where exists(select 1 from tb b
16 where a.Department_id in(50,80,90) and a.Manager_id=b.Manager_id
17* having count(Manager_id)=3)
SQL> /MANAGER_ID
----------
100
2 SELECT 100 Manager_id ,50 Department_id FROM dual UNION ALL
3 SELECT 124, 50 FROM dual UNION ALL
4 SELECT 124, 50 FROM dual UNION ALL
5 SELECT 124, 50 FROM dual UNION ALL
6 SELECT 124, 50 FROM dual UNION ALL
7 SELECT 100, 80 FROM dual UNION ALL
8 SELECT 149, 80 FROM dual UNION ALL
9 SELECT 149, 80 FROM dual UNION ALL
10 SELECT 100, 90 FROM dual UNION ALL
11 SELECT 124, 90 FROM dual
12 )
13 select distinct Manager_id
14 from tb a
15 where exists(select 1 from tb b
16 where a.Department_id in(50,80,90) and a.Manager_id=b.Manager_id and a.Department_id <> b.Department_id)
2 (select manager_id,count(manager_id) qq from (select distinct manager_id,dept_id from tt group by manager_id,dept_id) group by manager_id)
3 where qq=(select count(*) from (select distinct dept_id from tt));
MANAGER_ID
----------
100
楼主的要求等价于:
在select distinct manager_id,dept_id from tt group by manager_id,dept_id) group by manager_id
找出满足:manager_id的重复记录个数 等于dept_id个数 的manager_id
SELECT 100 Manager_id ,50 Department_id FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 100, 80 FROM dual UNION ALL
SELECT 149, 80 FROM dual UNION ALL
SELECT 149, 80 FROM dual UNION ALL
SELECT 100, 90 FROM dual UNION ALL
SELECT 124, 90 FROM dual
)
select manager_id
from (select distinct manager_id, department_id from tb) t
group by t.manager_id
having count(1) > 1
from tb
group by manager_id
having count(Department_id)=(select count(distinct Department_id) from tb);
select Manager_id from table group by Manager_id having count(distinct Department_id ) > 1这就求出了管理一个部门以上的经理。
WITH tab AS(
SELECT 100 Manager_id ,50 Department_id FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 124, 50 FROM dual UNION ALL
SELECT 100, 80 FROM dual UNION ALL
SELECT 149, 80 FROM dual UNION ALL
SELECT 149, 80 FROM dual UNION ALL
SELECT 100, 90 FROM dual UNION ALL
SELECT 124, 90 FROM dual
)
select manager_id from
(
select manager_id,count(department_id) ad from (select distinct manager_id,Department_id from tab) t group by manager_id
)
where ad=(select count(distinct Department_id) from tab)