select mangerid,name from emp where age < '' and mangerid not null;
select m.* from emp m , emp n where m.mangerid = n.id and m.age < n.age
create table emp(id int,name varchar(10),age int,mangerid int) 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 行) */
create table ATemp( id int, name varchar2(255), Age int, mangerid int); 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
select n.* from emp m , emp n 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并以编号,名字表示
select * 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)
我重新改了下:SELECT a.* FROM (SELECT * FROM emp WHERE id IN (SELECT DISTINCT mangerid FROM emp)) a, emp b 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)
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)