有两张表,表a,表b
表a:
id unitid photonumber1 photonumber2
===============================================
1 1111
2 12345678
3 22
4 1111
5 12345678
6 11
7
8 12345678表b:
photonumber unitid
============================
12345678 11
98765441 22
11109101 33想得到条件unitid包含11的所有表a记录,并包括表a字段unitid为空,但与表b通过photonumber关联,unitid也为11的记录.
查询结果表:id unitid photonumber1 photonumber2
================================================
1 1111
2 12345678
4 1111
5 12345678
6 11
8 12345678
表a:
id unitid photonumber1 photonumber2
===============================================
1 1111
2 12345678
3 22
4 1111
5 12345678
6 11
7
8 12345678表b:
photonumber unitid
============================
12345678 11
98765441 22
11109101 33想得到条件unitid包含11的所有表a记录,并包括表a字段unitid为空,但与表b通过photonumber关联,unitid也为11的记录.
查询结果表:id unitid photonumber1 photonumber2
================================================
1 1111
2 12345678
4 1111
5 12345678
6 11
8 12345678
解决方案 »
- 创建定时任务的时候,图形化界面生成的代码怎么不可以在命令窗口实现啊?
- ORA-03113: 通信通道的文件结束!
- cuug oracle dba 教程视频(持续更新...)
- 怎么解决oracle缓存问题
- 新手问问
- oracle spool 按指定文件大小导出数据问题
- 如何获取系统表的数据字典?
- oracle web server 一问(急)?
- 下面这段SQL语句在oracle中PL/SQL怎么写,急!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 新手提问
- 请问我如何把表中所有记录,用一个列的值去更改另外一个列的值
- 数据库导出后汉字变成乱码,有什么办法解决
where instr(unitid,'11')>0 or
exists
(select 1 from b where photonumber=a.photonumber1 or photonumber=a.photonumber2 and unitid='11')
這個意思?
insert into a select 1,'1111',NULL ,NULL
insert into a select 2,NULL ,NULL ,'12345678'
insert into a select 3,'22' ,NULL ,NULL
insert into a select 4,'1111',NULL ,NULL
insert into a select 5,NULL ,'12345678',NULL
insert into a select 6,'11' ,NULL ,NULL
insert into a select 7,NULL ,NULL ,NULL
insert into a select 8,NULL ,'12345678',NULLcreate table b(photonumber varchar(10),unitid varchar(10))
insert into b select '12345678','11'
insert into b select '98765441','22'
insert into b select '11109101','33'
go
select
a.*
from
a
where
a.unitid like '%11%'
or
(a.unitid is null
and
exists(select 1 from b where unitid='11' and photonumber in(a.photonumber1,a.photonumber2)))
go/*
id unitid photonumber1 photonumber2
----------- ---------- ------------ ------------
1 1111 NULL NULL
2 NULL NULL 12345678
4 1111 NULL NULL
5 NULL 12345678 NULL
6 11 NULL NULL
8 NULL 12345678 NULL
*/drop table a,b
go
where a.unitid like '%11%' or a.unitid is null
or
exists(select 1 from b where unitid='11' and photonumber in(a.photonumber1,a.photonumber2))
select a.* from a
where a.unitid like '%11%' or
(a.unitid is null
and
exists(select 1 from b where unitid='11' and photonumber in(a.photonumber1,a.photonumber2))libin_ftsafe(子陌红尘:TS for Banking Card)是对的