1)有一张表EMP:ID是关键字,代表员工编号;Salary是该员工薪水;ManID是该员工所属部门的部门经理的员工编号。需要注意的是:员工分普通员工和部门经理,部门经理的MANID和自己的ID相同。
其中EMP的一小部分数据如下:
ID Salary ManID
100 6500 100
200 5500 200
101 6600 100
102 6200 100
103 5100 100
104 6700 100
201 5800 200
202 4200 200
203 3500 200
204 2100 200
请写出一条SQL语句,以便查找到符合如下条件的员工ID:“薪水不少于所属部门的部门经理薪水的普通员工”
其中EMP的一小部分数据如下:
ID Salary ManID
100 6500 100
200 5500 200
101 6600 100
102 6200 100
103 5100 100
104 6700 100
201 5800 200
202 4200 200
203 3500 200
204 2100 200
请写出一条SQL语句,以便查找到符合如下条件的员工ID:“薪水不少于所属部门的部门经理薪水的普通员工”
where id <> manid
and id in
(
select id from emp a,emp b
where a.manid = b.id
and a.salary >= b.salary
)
where ID<>ManID and exists(Select * from tb
where ID=ManID and ManID=a.ManID and
Salary<=a.Salary)
(select t1.id,t1.Salary Salary1,t2.Salary Salary2 from emp t1,
(select * from emp where ID=ManID) t2
where t1.ID<>t1.ManID and t1.ManID=t2.ID)
where Salary1>Salary2;
select t1.id,t1.Salary Salary1 from emp t1,
(select * from emp where ID=ManID) t2
where t1.ID<>t1.ManID and t1.ManID=t2.ID and t1.Salary>t2.Salary;
我的MSN:[email protected]
我是北京的~希望能和各位叫个朋友~有时间一起吃个饭
/
Insert into emp
Select 100 ,6500 ,100 from dual
Union select 200 ,5500 , 200 from dual
Union select 101 ,6600 , 100 from dual
Union select 102 ,6200 , 100 from dual
Union select 103 ,5100 , 100 from dual
Union select 104 ,6700 , 100 from dual
Union select 201 ,5800 , 200 from dual
Union select 202 ,4200 , 200 from dual
Union select 203 ,3500 , 200 from dual
Union select 204 ,2100 , 200 from dual
/
Select * from emp a
where ID<>ManID and exists(Select * from emp
where ID=ManID and ManID=a.ManID and
Salary<=a.Salary) -----------------------
ID Salary ManID
-----------------------
101 6600 100
104 6700 100
201 5800 200
where id <> manid
and id in
(
select a.id from emp a,emp b
where a.manid = b.id
and a.salary >= b.salary
)少了个a.id
这个SQL绝对是对的..测试过的..