有如下Employee表结构:
| id | age | manager |
| 1 | 24 | 5 |
| 2 | 26 | 5 |
| 3 | 28 | 4 |
| 4 | 27 | NULL |
| 5 | 25 | NULL |
| 6 | 26 | 4 |问SQL语句,
如何查询年龄比经理(manager)小的员工ID。
| id | age | manager |
| 1 | 24 | 5 |
| 2 | 26 | 5 |
| 3 | 28 | 4 |
| 4 | 27 | NULL |
| 5 | 25 | NULL |
| 6 | 26 | 4 |问SQL语句,
如何查询年龄比经理(manager)小的员工ID。
from employee a
where exists(select 1 from age > a.age and a.manager = id and manager is null)
id
from
Employee a
left join
Employee b
on
a.manager=b.id
and
a.age<b.age
from Employee a
join Employee b
on a.manager=b.id and b.manager is null and a.age<b.age
if object_id('[Employee]') is not null drop table [Employee]
go
create table [Employee]([id] int,[age] int,[manager] int)
insert [Employee]
select 1,24,5 union all
select 2,26,5 union all
select 3,28,4 union all
select 4,27,null union all
select 5,25,null union all
select 6,26,4
---查询---
select
a.id
from
Employee a
left join
Employee b
on
a.manager=b.id
where
a.age<b.age---结果---
id
-----------
1
6(所影响的行数为 2 行)
-- Author: happyflystone
-- Version:V1.001
-- Date:2009-05-18 14:43:35
-------------------------------------- Test Data: Employee
If object_id('Employee') is not null
Drop table Employee
Go
Create table Employee(id int,age int,manager int)
Go
Insert into Employee
select 1,24,5 union all
select 2,26,5 union all
select 3,28,4 union all
select 4,27,NULL union all
select 5,25,NULL union all
select 6,26,4
Go
--Start
select *
from employee a
where exists(select 1 from employee where age > a.age and a.manager = id and manager is null)
--Result:
/*
id age manager
----------- ----------- -----------
1 24 5
6 26 4(所影响的行数为 2 行)*/
--End
where exists(select 1 from age > a.age and a.manager = id and manager is null)
修正
SQL codeselect id from employee a
where exists(select 1 from employee where age > a.age and a.manager = id and manager is null)
我这个最好理解。
select id from employee where are < all( select age from employee where manager is not null) and manager is null
select a.*
from employee a,employee b
where a.age<b.age and a.manager=b.idselect id from employee a
where exists(select 1 from employee where age > a.age and a.manager = id and manager is null)
上面两个代码有什么区别吗?
比如数据量多的时候,在效率等方面?
where exists(select 1 from employee where age > a.age and a.manager = id and manager is null)
那我用下面的语句select a.*
from employee a,employee b
where a.age<b.age and a.manager=b.id可以有结果的啊。
输出:
| id | age | manager |
| 1 | 24 | 5 |
| 6 | 26 | 4 |
测试不行啊
select a.*from employee a,employee b
where a.age<b.age and a.manager=b.idselect id from employee a
where exists(select 1 from employee where age > a.age and a.manager = id and manager is null)
上面两个代码有什么区别吗?
比如数据量多的时候,在效率等方面?
IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE NAME='emp')
drop table emp
create table emp
(id char(2) not null,
age int not null,
manager char(2) NULL
)insert into emp
select '1',24,'5'
Union all
select '2',26,'5'
union all
select '3',28,'4'
union all
select '4',27,''
union all
select '5',25,''
union all
select '6',26,'4' select * from emp
with manager
as
(select id ,age,manager
from emp
where manager='')select emp.id
from emp,manager
where emp.manager=manager.id
and
emp.age<manager.age
--创建所需表环境
IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE NAME='emp')
drop table emp
create table emp
(id char(2) not null,
age int not null,
manager char(2) NULL
)insert into emp
select '1',24,'5'
Union all
select '2',26,'5'
union all
select '3',28,'4'
union all
select '4',27,''
union all
select '5',25,''
union all
select '6',26,'4' select * from emp --利用CTE
;with manager
as
(select id ,age,manager
from emp
where manager='')select emp.id
from emp,manager
where emp.manager=manager.id
and
emp.age<manager.age上面的有点缺陷少了一个“;”
| id | age | manager |
| 1 | 24 | 5 |
| 2 | 26 | 5 |
| 3 | 28 | 4 |
| 4 | 27 | NULL |
| 5 | 25 | NULL |
| 6 | 26 | 4 | 问SQL语句,
如何查询年龄比经理(manager)小的员工ID。
有age<manager的记录吗