select a.*
from t_master a
inner join t_serv b
on a.id=b.id
where not exists(select 1 from t_serv where id=b.id and flag<>1)
from t_master a
inner join t_serv b
on a.id=b.id
where not exists(select 1 from t_serv where id=b.id and flag<>1)
(select distinct id from t_serv where flag=1)
(select id from t_serv where flag=1 group by id having count(*)>1)
select A.id ,A.name from t_master A inner join
(select id,count(*) num from t_serv where flag=1 group by id ) B
on A.id=B.id
inner join (select id,count(*) num from t_serv group by id ) C
on B.id=C.id and B.num=C.num
2): 再从t_serv表中把第一步的那些id过滤出去,剩下的就都是=1的id了
3):从t_master表中找出含有第2步的id的纪录,就是结果了。
select * from t_master where id in ( --(3)
select distinct id from t_serv where id not in ( --(2)
select id from t_serv where flag <> 1 -- (1)
)
)
Where ID Not In (Select DIstinct ID from t_serv Where flag=0)这个比一楼的效率更高。
那如果flag 不仅仅只有1,0这两个值, 怎幺办?
可以考虑把flag转换成1,0来做!
楼上的:
那如果flag 不仅仅只有1,0这两个值, 怎幺办?------------------------------------
小改动一下即可Select * from t_master
Where ID Not In (Select DIstinct ID from t_serv Where flag<>1)
edgethinking(向JAVA骨灰级进军) 的方法条理比较清晰可以达到目的paoluo(一天到晚游泳的鱼) 的方法如果在主表里有而从表里没有的ID也会被选出来
----------------------------------------
哦,这一点的确没有考虑到。看你的例子,我以为主表有的ID细表中一定有的。那用edgethinking的吧。
select *
from t_master
where not exists(
select*
from t_serv
where t_master.id=id
and flag=0)
select *
from t_master
where not exists(
select*
from t_serv
where t_master.id=id
and flag<>1)
and exists( select*
from t_serv
where t_master.id=id)
select *
from t_master
where id not in (select distinct id form t_serv where flag<>1)
and id in (select distinct id from t_serv) //去掉有主表无从表的记录。
(
id int, name varchar(20)
)create table t_serv
(
id int,sid int,flag int,re varchar(100)
)goinsert t_master
select 1,'A' union select 2,'B' union select 3,'C' union select 4,'D'insert t_serv
select 1,1,1,'' union
select 1,2,0,'' union
select 2,1,1,'' union
select 3,1,1,'' union
select 3,2,1,'' union
select 4,1,1,'' union
select 4,2,0,'' union
select 4,3,1,''select distinct A.id,(select name from t_master where id=A.id) as name
from t_serv A
where A.id not in (select distinct id from t_serv where flag=0)
--删除测试数据
drop table t_master
drop table t_serv
-- 结果
2 B
3 C
from t_master a,t_serv b
where not exists(select 1 from t_serv where id=a.id and flag<>1) and a.id=b.id