有两张表:
员工表(id,name,department)id name department
001 小王 部门A
002 小张 部门B
003 小明 部门C培训表(id,person_id,train)
id person_id train time
001 001 培训A 2007.02.23
002 001 培训B 2009.04.21
003 002 培训A 2010.04.02
004 002 培训C 2011.01.06
求近三年(2009,2010,2011)没有参加过“培训A”的人,结果是“小王,小明”。这个sql怎么写了?很久不用了,忘记了差不多,求指点,谢谢!
员工表(id,name,department)id name department
001 小王 部门A
002 小张 部门B
003 小明 部门C培训表(id,person_id,train)
id person_id train time
001 001 培训A 2007.02.23
002 001 培训B 2009.04.21
003 002 培训A 2010.04.02
004 002 培训C 2011.01.06
求近三年(2009,2010,2011)没有参加过“培训A”的人,结果是“小王,小明”。这个sql怎么写了?很久不用了,忘记了差不多,求指点,谢谢!
Where ID Not In (Select person_id From Train Where Year(time) In ('2009','2010','2011') And Train = '培训A')
Declare @Employ Table(
ID Varchar(20) Primary Key,
Name Varchar(20),
Department Varchar(20)
)Declare @Train Table(
ID Varchar(20) Primary Key,
person_id Varchar(20),
train Varchar(20),
time DateTime
)Insert Into @Employ
Select '001', '小王', '部门A'
Union Select '002', '小张', '部门B'
Union Select '003', '小明', '部门C'Insert Into @Train
Select '001', '001', '培训A', '2007.02.23'
Union Select '002', '001', '培训B', '2009.04.21'
Union Select '003', '002', '培训A', '2010.04.02'
Union Select '004', '002', '培训C', '2011.01.06'Select * From @Employ
Where ID Not In (Select person_id From @Train
Where Year(time) In ('2009','2010','2011') And Train = '培训A')
select @ret
select @ret
--> 测试数据:[员工表]
if object_id('[员工表]') is not null drop table [员工表]
create table [员工表]([id] varchar(3),[name] varchar(4),[department] varchar(5))
insert [员工表]
select '001','小王','部门A' union all
select '002','小张','部门B' union all
select '003','小明','部门C'
--> 测试数据:[培训表]
if object_id('[培训表]') is not null drop table [培训表]
create table [培训表](
[id] varchar(3),
[person_id] varchar(3),
[train] varchar(5),
[time] datetime
)
insert [培训表]
select '001','001','培训A','2007.02.23' union all
select '002','001','培训B','2009.04.21' union all
select '003','002','培训A','2010.04.02' union all
select '004','002','培训C','2011.01.06'
select * from [员工表] a
where not exists(
select 1 from [培训表] b where a.id=b.person_id
and b.[time] between '2009-01-01' and '2012-12-31'
and b.train='培训A')
/*
id name department
001 小王 部门A
003 小明 部门C
*/貌似楼上的有问题哦。
declare @ret varchar(500)select @ret =isnull( @ret+',','')+name from ta a where not exists(select 1 from tb b where year(time) in( 2011,2010,2009) and b.person_id = a.id and b.train = '培训A')
select @ret
go
use test;if object_id('[员工表]') is not null drop table [员工表]
create table [员工表]([id] varchar(3),[name] varchar(4),[department] varchar(5))
insert [员工表]
select '001','小王','部门A' union all
select '002','小张','部门B' union all
select '003','小明','部门C'
--> 测试数据:[培训表]
if object_id('[培训表]') is not null drop table [培训表]
create table [培训表](
[id] varchar(3),
[person_id] varchar(3),
[train] varchar(5),
[time] datetime
)
insert [培训表]
select '001','001','培训A','2007.02.23' union all
select '002','001','培训B','2009.04.21' union all
select '003','002','培训A','2010.04.02' union all
select '004','002','培训C','2011.01.06'select distinct a.id ,a.name ,a.department ,b.train from [员工表] a,[培训表] b
where not exists(
select 1 from [培训表] b where a.id=b.person_id
and b.[time] between '2009-01-01' and '2012-12-31'
and b.train='培训A')不行呢
declare @ret varchar(500)select @ret =isnull( @ret+',','')+name from ta a where not exists(select 1 from tb b where year(time) in( 2011,2010,2009) and b.person_id = a.id and b.train = '培训A')
有两张表:
员工表(id,name,department)id name department
001 小王 部门A
002 小张 部门B
003 小明 部门C培训表(id,person_id,train)
id person_id train time
001 001 培训A 2007.02.23
002 001 培训B 2009.04.21
003 002 培训A 2010.04.02
004 002 培训C 2011.01.06
求近三年(2009,2010,2011)没有参加过“培训A”的人。
显示结果是:
person_id name departmen train time
001 小王 部门A 培训A 2007.02.23
003 小明 部门C 培训A 2010.04.02
go
use test;if object_id('[员工表]') is not null drop table [员工表]
create table [员工表]([id] varchar(3),[name] varchar(4),[department] varchar(5))
insert [员工表]
select '001','小王','部门A' union all
select '002','小张','部门B' union all
select '003','小明','部门C'
--> 测试数据:[培训表]
if object_id('[培训表]') is not null drop table [培训表]
create table [培训表](
[id] varchar(3),
[person_id] varchar(3),
[train] varchar(5),
[time] datetime
)
insert [培训表]
select '001','001','培训A','2007.02.23' union all
select '002','001','培训B','2009.04.21' union all
select '003','002','培训A','2010.04.02' union all
select '004','002','培训C','2011.01.06'
Where [培训表].ID Not In (Select person_id From [培训表]
Where Year(time) In ('2009','2010','2011') And Train = '培训A')结果是:001 小王 部门A 培训A 2007-02-23 00:00:00.000
002 小张 部门B 培训A 2007-02-23 00:00:00.000
003 小明 部门C 培训A 2007-02-23 00:00:00.000
001 小王 部门A 培训A 2010-04-02 00:00:00.000
002 小张 部门B 培训A 2010-04-02 00:00:00.000
003 小明 部门C 培训A 2010-04-02 00:00:00.000
001 小王 部门A 培训C 2011-01-06 00:00:00.000
002 小张 部门B 培训C 2011-01-06 00:00:00.000
003 小明 部门C 培训C 2011-01-06 00:00:00.000
From [员工表],[培训表]
Where [员工表].ID Not In (Select person_id From [培训表]
Where Year(time) In ('2009','2010','2011') And Train = '培训A')结果是:
001 小王 部门A 培训A 2007-02-23 00:00:00.000
001 小王 部门A 培训B 2009-04-21 00:00:00.000
001 小王 部门A 培训A 2010-04-02 00:00:00.000
001 小王 部门A 培训C 2011-01-06 00:00:00.000
003 小明 部门C 培训A 2007-02-23 00:00:00.000
003 小明 部门C 培训B 2009-04-21 00:00:00.000
003 小明 部门C 培训A 2010-04-02 00:00:00.000
003 小明 部门C 培训C 2011-01-06 00:00:00.000
From [员工表] left join [培训表] on [员工表].id = [培训表].person_id
Where [员工表].ID Not In (Select person_id From [培训表]
Where Year(time) In ('2009','2010','2011') And Train = '培训A')结果:
001 小王 部门A 培训A 2007-02-23 00:00:00.000
001 小王 部门A 培训B 2009-04-21 00:00:00.000
003 小明 部门C NULL NULL
where not exists(
select 1 from [培训表] b where a.id=b.person_id
and b.[time] between '2009-01-01' and '2012-12-31'
and b.train='培训A')2.既然员工表连接了培训表,为什么不定义连接条件?
select distinct a.id ,a.name ,a.department ,b.train from [员工表] a,[培训表] b
where a.id=b.person_id
and not exists(
select 1 from [培训表] b where a.id=b.person_id
and b.[time] between '2009-01-01' and '2012-12-31'
and b.train='培训A')
[/code]
其实你是想找(2009,2010,2011)没有参加过“培训A”的人,参加了什么培训吧?