如表
emp
选出所有比自己下属年龄小的主管的id,name并以编号,名字表示
id name age mangerid
1 a 23 null
2 b 26 5
3 c 21 1
4 d 26 3
5 e 20 3
emp
选出所有比自己下属年龄小的主管的id,name并以编号,名字表示
id name age mangerid
1 a 23 null
2 b 26 5
3 c 21 1
4 d 26 3
5 e 20 3
where age < ''
and mangerid not null;
where m.mangerid = n.id and m.age < n.age
insert into emp values(1, 'a', 23, null)
insert into emp values(2, 'b', 26, 5)
insert into emp values(3, 'c', 21, 1)
insert into emp values(4, 'd', 26, 3)
insert into emp values(5, 'e', 20, 3)
goselect n.* from emp m , emp n
where m.mangerid = n.id and m.age < n.agedrop table emp/*
id name age mangerid
----------- ---------- ----------- -----------
1 a 23 NULL
3 c 21 1(所影响的行数为 2 行)
*/
insert into ATemp(id, name, age, mangerid)
select 1, 'a', 23, null from dual
union all
select 2, 'b', 26, 5 from dual
union all
select 3, 'c', 21, 1 from dual
union all
select 4, 'd', 26, 3 from dual
union all
select 5, 'e', 20, 3 from dual;
Commit;select Distinct ATemp.MangerId
from ATemp, (select Id MangerId, Age
from ATemp
where Id In (select MangerId
from ATemp)) Manger
where ATemp.MangerId = Manger.MangerId
and ATemp.Age >= Manger.Age;
MANGERID
---------------------------------------
3
5
where m.mangerid = n.id and m.age < n.age
--比任意一个下属年龄小,语句
SELECT a.*
FROM (SELECT * FROM emp WHERE empno IN (SELECT DISTINCT mangerid FROM emp)) a, emp b
WHERE b.mangerid = a.empno AND
a.age < ANY b.age;
选出所有比自己所有下属年龄小的主管的id,name并以编号,名字表示
from emp t
where (select count(case
when age <= t.age then
1
end)
from emp
start with managerid = t.id
connect by prior id = managerid) = 0
and exists (select 1 from emp where managerid = t.id)
WHERE b.mangerid = a.id AND a.age < all (SELECT c.age FROM emp c
WHERE c.mangerid = a.id);
这个运行ok了
select *
from emp1 t1
where not exists (select 1
from emp1 t2
where t2.managerid = t1.id
and t2.age < t1.age)
and id in (select distinct managerid from emp1)