56,822
社区成员
发帖
与我相关
我的任务
分享各位大神急求一条联查sql
主表 A
子表 B一对多的关系，需求是 子表中stateId 都等于5011的情况下 主表的数据才能查出来 来个大哥解脱我
...全文
请发表友善的回复…
发表回复
Marco_pei 2019-10-25
- 打赏
- 举报
select * from a where em_info_id in (select em_info_id from b where state_id=5011)
有点意思！ 2019-10-25
- 打赏
- 举报
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)
有点意思！ 2019-10-25
- 打赏
- 举报
select * from a
where a.em_info_id not in
(select distinct(b.em_info_id) from b
where b.state_id = 5000)
有点意思！ 2019-10-25
- 打赏
- 举报
暂时想到
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
)
X沐浴阳光 2019-10-23
- 打赏
- 举报
select a.* from a,b where a.em_info_id=b.em_info_id and b.em_info_id=5011;
纳尼刘君 2019-10-22
- 打赏
- 举报
select * from
主表 a
where exists(select * from 子表 b where a.em_info_id=b.em_info_id and b.stateId=5011)
