主表 A
子表 B
一对多的关系,需求是 子表中stateId 都等于5011的情况下 主表的数据才能查出来 来个大哥解脱我
子表 B
一对多的关系,需求是 子表中stateId 都等于5011的情况下 主表的数据才能查出来 来个大哥解脱我
解决方案 »
- 请教一条SQL,结果返回两个统计数字~~~~~~~~~~~
- 求高手帮写个php+MySQL经营管理系统的数据库设计或需求分析
- PostgreSQL9.0装上去后,用pgAdminⅢ打开连不上数据库
- where 写法的问题
- 求preparedStatement 用法
- LINUX如何查看MYSQL日志存放在何处?
- 跪求一mysql查询语句???/
- PostgreSQL有没有支持C++语言的引擎或接口可用?
- 头大了
- 运行安装接口程序出现以下错误: You have an error in your SQL syntax; check the manual that corr
- mysql分组取出组里面指定条件的前一条数据
- 配置spark-sql配置完成后./bin/spark-sql一直进不去。卡在那里
主表 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
)