17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE A
(ID integer,
DIS integer,
branch integer,
trans integer)
insert into A (id,dis,branch,trans) values (68001,568,568,001);
CREATE TABLE B
(ID integer,
ID_APP integer)
insert into B (id,id_app) values (68001,68001);
CREATE TABLE C
(ID integer,
DIS integer,
branch integer,
trans integer
status integer)
insert into C(id,dis,branch,trans,status) values (68001,568,568,001,02);
insert into C (id,dis,branch,trans,status) values (68001,218,218,005,02);
select a.id as id_app,b.id,c.status from A a
left join C c on a.dis = c.dis
and a.branch = c.branch
and a.trans = c.trans
and a.id = c.id
left join B b on c.id=b.id
select a.id as id_app,b.id,c.status from A a
left join C c on a.dis = c.dis
and a.branch = c.branch
and a.trans = c.trans
and nvl(a.id,c.id) = c.id
left join B b on c.id=b.id
试试这个,得到的结果就不行了
-- 这里跑的是你原本的语句,没有问题。
SQL> CREATE TABLE A(ID integer,DIS integer,branch integer,trans integer);
Table created
SQL> insert into A (id,dis,branch,trans) values (68001,568,568,001);
1 row inserted
SQL> CREATE TABLE B(ID integer, ID_APP integer);
Table created
SQL> insert into B(id,id_app) values (68001,68001);
1 row inserted
SQL> CREATE TABLE C(ID integer,DIS integer,branch integer,trans integer,status integer);
Table created
SQL> insert into C(id,dis,branch,trans,status) values (68001,568,568,001,02);
1 row inserted
SQL> insert into C (id,dis,branch,trans,status) values (68001,218,218,005,02);
1 row inserted
SQL> commit ;
Commit complete
SQL> select a.id as id_app,b.id,c.status from A a
2 left join C c on a.dis = c.dis
3 and a.branch = c.branch
4 and a.trans = c.trans
5 and a.id = c.id
6 left join B b on c.id=b.id ;
ID_APP ID STATUS
--------------------------------------- --------------------------------------- ---------------------------------------
68001 68001 2
SQL>
-- 使用一个 NVL函数
select a.id as id_app,isnull(b.id,a.id) id ,c.status from A a
left join C c on a.dis = c.dis
and a.branch = c.branch
and a.trans = c.trans
and a.id = c.id
left join B b on c.id=b.id