A表一字段 Col_a 取值 True/False ,对应在 B表及 C表为 Col_b,Col_c想得到 A表中 所有对应在 B,C 中为 True 的记录。(映射关系 A.id=B.id=C.id)亦即,只要 Col_b = true 或 Col_c = true 就选出 A 表对应记录(注意:表 B 和 C 中对应 a.id 可能同时存在 True 和 False 记录,若采用以下写法,会选出两条记录)例如 B表中有一条为 True 但 C表中有两条:True 和 Falseselect A.* from A
left join B on a.id=b.id
left join c on a.id=c.id
where b.col_b='true' or c.col_c='true'=========================================请指教,谢谢 !
left join B on a.id=b.id
left join c on a.id=c.id
where b.col_b='true' or c.col_c='true'=========================================请指教,谢谢 !
select *
from a
where exists (select 1 from b where id = a.id and b.col_b = 'true')
or exists (select 1 from c where id = a.id and c.col_c = 'true')
create table tb(id int,col_b bit)
create table tc(id int,col_c bit)
insert into ta select 1 union select 2 union select 3 union select 4
insert into tb select 1,1 union select 2,0 union select 3,0 union select 4,1
insert into tc select 1,1 union select 2,1 union select 3,0 union select 4,0
go
select a.id from ta a left join tb b on a.id=b.id left join tc c on a.id=c.id where b.col_b=1 or c.col_c=1
go
drop table ta,tb,tc
/*
id
-----------
1
2
4(3 行受影响)
*/
id COL_A
1001 TRUE
1002 TRUE
1003 FALSE表B:
id COL_B
1002 TRUE
1005 TRUE
1009 FALSE表C:
id COL_C
1002 TRUE
1002 FALSE
1004 FALSE====================
想在结果中显示:
ID COL_B COL_C1002 TRUE TRUE
(case when exists (select 1 from b where id = a.id and col_b = 'true') then 'true' else 'false' end)col_b,
(case when exists (select 1 from c where id = a.id and col_c = 'true') then 'true' else 'false' end)col_c
from A
left join B on a.id=b.id
left join c on a.id=c.id
where b.id is not null and c.id is not null
select a.id,
(select col_b from b where b.id=a.id and b.col_b='true') col_b,
(select col_c from c where c.id=a.id and c.col_c='true') col_c
from a left join b
on a.id=b.id left join c
on a.id=c.id
where col_b^col_c='true'