SQL SERVER 2008的EXCEPT和INTERSECT

EXCEPT：用第一个集合与第二个集合比较，返回只存在于第一个集合，但不在第二个集合中的值。

INTERSECT:比较两个集合，返回两个集合都有的行。

我们新建一个表

CREATE TABLE projectPerson

(

　　personId varchar(10),

　　projectId varchar(10),

　　PRIMARY KEY (personId,projectId)

)

GO

INSERT INTO projectPerson Values('joeb','projBlg')
INSERT INTO projectPerson Values('joeb','projLitter')
INSERT INTO projectPerson Values('fredf','projBlg')
INSERT INTO projectPerson Values('homerr','projLitter')
INSERT INTO projectPerson Values('stevegr','projBlg')
INSERT INTO projectPerson Values('stevegr','projLitter')

GO

--查看谁参与了其中一个项目获两个项目都参加

select personId
from projectPerson
where projectId='projBig'
union
select personId
from projectPerson
where projectId='projLitter'

 

 

查看只参加projLittle项目但不参与projBig项目的人

以前sql server 2000的写法

select personId
from projectPerson as projLittle
where projectId='projLitter'
  and not exists (select *
     from projectPerson as projBig
       where projBig.projectId='projBig'
       and projBig.personId=projLittle.personId)

现在只需要用except即可

select personId
from projectPerson
where projectId='projLitter'    
except
select personId
from projectPerson
where projectId='projBig'

 

两个项目都参加的人的信息可用intersect:

select personId
from projectPerson
where projectId='projLitter'    
intersect
select personId
from projectPerson
where projectId='projBig'