create table t1(PersonId int,TaskB varchar(10), TaskC VARChar(10)) insert into t1 select 1,'true','true' insert into t1 select 2,'true','false' insert into t1 select 3,'true','true' create table t2(PersonId int) insert into t2 select 1 insert into t2 select 3 create table t3(PersonId int) insert into t3 select 3 go select a.personid, (case when a.taskB='true' and b.personid is null then 'false' else '' end) taskB, (case when a.taskC='true' and c.personid is null then 'false' else '' end) taskC from t1 a left join t2 b on a.personid=b.personid left join t3 c on a.personid=c.personid /* personid taskB taskC ----------- ----- ----- 1 false 2 false 3 (3 行受影响)*/ go drop table t1,t2,t3
insert into t1 select 1,'true','true'
insert into t1 select 2,'true','false'
insert into t1 select 3,'true','true'
create table t2(PersonId int)
insert into t2 select 1
insert into t2 select 3
create table t3(PersonId int)
insert into t3 select 3
go
select a.personid,
(case when a.taskB='true' and b.personid is null then 'false' else '' end) taskB,
(case when a.taskC='true' and c.personid is null then 'false' else '' end) taskC
from t1 a left join t2 b on a.personid=b.personid
left join t3 c on a.personid=c.personid
/*
personid taskB taskC
----------- ----- -----
1 false
2 false
3 (3 行受影响)*/
go
drop table t1,t2,t3
搞定!
谢谢!