SQL 语句

if exists(select * from sysobjects where name='dept')
drop table dept
create table dept
(
  ID varchar(10) not null,//员工编号
  Name varchar(20) not null,//员工姓名
  Age int not null,//年龄
  Manager varchar(10) //上司ID
)
go
begin transaction
insert into dept values('1001','perfect',20,null)
insert into dept values('1002','sarsa',25,'1001')
insert into dept values('1003','peter',26,'1001')
insert into dept values('1004','maliya',23,'1003')
insert into dept values('1005','mary',30,'1002')
insert into dept values('1006','other',26,'1009')
go


select * from dept

Q:问年龄小于下属的主管的ID，Name。如何查询？-----

 

SELECT  ID，NAME  FROM DEPT T WHERE EXISTS （SELECT 1 FROM DEPT WHERE MANAGER=T.ID AND AGE>T.AGE）

 

select distinct t.id,t.name from dept t inner join dept p on t.id=p.manager where t.age<p.age