有表a
id表b
id stat
其中b表状态stat值有0,1,2
要从a表中查询出id,且在b表中不存在这id的状态是0或1的
表a,b数据如下WITH a AS(SELECT '1' AS ID FROM dual UNION ALL
SELECT '2' AS ID FROM dual UNION ALL
SELECT '3' AS ID FROM dual UNION ALL
SELECT '4' AS ID FROM dual UNION ALL
SELECT '5' AS ID FROM dual UNION ALL
SELECT '6' AS ID FROM dual ),
b AS(SELECT '1' AS ID,'2' AS stat FROM dual UNION ALL
SELECT '2' AS ID,'0' AS stat FROM dual UNION ALL
SELECT '3' AS ID,'1' AS stat FROM dual UNION ALL
SELECT '4' AS ID,'2' AS stat FROM dual )想要查询出的结果如下:id stat
1 2
4 2
5
6
id表b
id stat
其中b表状态stat值有0,1,2
要从a表中查询出id,且在b表中不存在这id的状态是0或1的
表a,b数据如下WITH a AS(SELECT '1' AS ID FROM dual UNION ALL
SELECT '2' AS ID FROM dual UNION ALL
SELECT '3' AS ID FROM dual UNION ALL
SELECT '4' AS ID FROM dual UNION ALL
SELECT '5' AS ID FROM dual UNION ALL
SELECT '6' AS ID FROM dual ),
b AS(SELECT '1' AS ID,'2' AS stat FROM dual UNION ALL
SELECT '2' AS ID,'0' AS stat FROM dual UNION ALL
SELECT '3' AS ID,'1' AS stat FROM dual UNION ALL
SELECT '4' AS ID,'2' AS stat FROM dual )想要查询出的结果如下:id stat
1 2
4 2
5
6
解决方案 »
- Oracle 实时同步 Postgre 如何实现?
- Oracle 10G 在OLE DB 读取数据的问题
- Oracle监听服务启动不了
- Oracle EBS系统非常难的一个问题(获取销售价格)
- 时间维度表里的数据是怎么自动生成的?
- ORA-00933: SQL 命令未正确结束
- 想破头都没有想出来的sql语句,跟树形结构有关的
- 一个关于登陆oracle server manager得简单问题
- 『入门』新建用户表空间时候可以选择字典模式的管理以及本地管理,请问,这有什么区别和各自有什么好处呢?
- 急!请说一下两个表左外结合查询的意思。
- 关于如何冷备份恢复数据库
- 求教!oracle 问题 。刚安装的oracle 在创建 数据库最后一步提示tns 协议适配器错误.
select a.id as aid, b.id as bid,b.stat
from a
left join b on a.id = b.id
where b.stat not in (0,1)
or b.stat is null;
试试这个
where not exists (select 1 from b where a.id=b.id)
and b.statin (0,1)
#6楼 效果出来,不过我听说写sql死都不要用Not In
#7楼 sql有问题,我不懂这个什么意思,要改哪些东西
目前,我是按#1楼的孙俪童鞋写的sql,select a.id as aid, b.id as bid,b.stat
from a
left join b on a.id = b.id
where b.stat ='2'
or b.stat is null;欢迎指正