17,086
社区成员
发帖
与我相关
我的任务
分享
SQL> select * from tablet;
RELA_ID STRUCT_ID FILE_TYPE USER_NO AUTH_TYPE AUTH_CODE
---------- ---------- ---------- ---------- ---------- ----------
1951 3 01 YYY 02 01
1952 3 01 YYY 02 02
1953 3 02 YYY 02 02
1954 4 01 YYY 02 01
SQL>
SQL> select nvl(a.rela_id,b.rela_id) rela_id, nvl(a.struct_id,b.struct_id) struct_id,nvl(a.file_type,b.file_type) file_type, nvl(a.user_no,b.user_no) user_no,
2 nvl(a.auth_type,b.auth_type) auth_type, nvl(a.auth_code,b.auth_code) auth_code , case when a.auth_code is null then null else b.auth_code end auth_code2,
3 case when a.rela_id is null then null else b.rela_id end rela_id2
4 from (select * from tablet where auth_code='01') a full outer join (select * from tablet where auth_code='02') b
5 on a.struct_id=b.struct_id and a.file_type=b.file_type and a.user_no=b.user_no and a.auth_type=b.auth_type ;
RELA_ID STRUCT_ID FILE_TYPE USER_NO AUTH_TYPE AUTH_CODE AUTH_CODE2 RELA_ID2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1951 3 01 YYY 02 01 02 1952
1954 4 01 YYY 02 01
1953 3 02 YYY 02 02
SQL>