select * from 主表 a where exists(select * from 子表 b where a.em_info_id=b.em_info_id and b.stateId=5011)
select a.* from a,b where a.em_info_id=b.em_info_id and b.em_info_id=5011;
select * from a where a.em_info_id not in (select distinct(b.em_info_id) from b where b.state_id = 5000)
select * from a where a.em_info_id not in (select distinct(b.em_info_id) from b where b.state_id = 5000) 或者下面的都可行 select * from a left join b on a.em_info_id = b.em_info_id where a.em_info_id not in (select distinct(b.em_info_id) from b where b.state_id = 5000)
select * from a where em_info_id in (select em_info_id from b where state_id=5011)
暂时想到 select * from a left join b on a.em_info_id = b.em_info_id where a.em_info_id in ( select aa.em_info_id from ( select b.em_info_id, count(b.state_id) as bs1 from b -- b表中 em_info_id各分组下5011的总条数 where b.state_id = 5011 group by b.em_info_id, b.state_id ) aa inner join ( select b.em_info_id, count(b.em_info_id) as bs2 from b -- b表中 em_info_id分组的条数 group by b.em_info_id ) cc on aa.em_info_id = cc.em_info_id and bs1 = bs2 -- em_info_id各分组下5011的总条数 = em_info_id各分组的总条数; 如果相等, 说明state_id都等于5011 )
主表 a
where exists(select * from 子表 b where a.em_info_id=b.em_info_id and b.stateId=5011)
where a.em_info_id not in
(select distinct(b.em_info_id) from b
where b.state_id = 5000)
where a.em_info_id not in
(select distinct(b.em_info_id) from b
where b.state_id = 5000)
或者下面的都可行
select * from a
left join b on a.em_info_id = b.em_info_id
where a.em_info_id not in
(select distinct(b.em_info_id) from b
where b.state_id = 5000)
select * from a
left join b on a.em_info_id = b.em_info_id
where a.em_info_id in
(
select aa.em_info_id from
(
select b.em_info_id, count(b.state_id) as bs1
from b -- b表中 em_info_id各分组下5011的总条数
where b.state_id = 5011
group by b.em_info_id, b.state_id
) aa
inner join
(
select b.em_info_id, count(b.em_info_id) as bs2
from b -- b表中 em_info_id分组的条数
group by b.em_info_id
) cc on aa.em_info_id = cc.em_info_id
and bs1 = bs2 -- em_info_id各分组下5011的总条数 = em_info_id各分组的总条数; 如果相等, 说明state_id都等于5011
)