两表多条件关联查询问题
有两张表,表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
[解决办法]
没看懂什么意思:(
[解决办法]
select * from a
where instr(unitid, '11 ')> 0 or
exists
(select 1 from b where photonumber=a.photonumber1 or photonumber=a.photonumber2 and unitid= '11 ')
這個意思？
[解决办法]
create table a(id int,unitid varchar(10),photonumber1 varchar(10),photonumber2 varchar(10))
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 ',NULL
create 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
[解决办法]
以上示例在SQL Server 2000环境下测试通过，使用了与Oracle通用的语法，Oracle下应该可用。
[解决办法]
select a.* from a
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)是对的
