--A表
PART_NO PART_NAME J_NO
-------- ---------- -------
TEST1 NAME1 MJ1
TEST2 NAME2 MJ2
TEST3 NAME3 NULL
MJ1 J001 NULL
MJ2 J002 NULL--B表
mk_no part_no
-------- ----------
M01 TEST1
M02 TEST2
M03 TEST3
--想到的结果如下(仅限用SELECT)B.mk_no A.PART_NO A.PART_NAME A.J_NO PART_NAME
-------- -------- ---------- ------- ----------
M01 TEST1 NAME1 MJ1 J001
M02 TEST2 NAME2 MJ2 J002
M03 TEST3 NAME3 NULL NULL直接使用SELECT 加外链接可否实现?该如何实现?
PART_NO PART_NAME J_NO
-------- ---------- -------
TEST1 NAME1 MJ1
TEST2 NAME2 MJ2
TEST3 NAME3 NULL
MJ1 J001 NULL
MJ2 J002 NULL--B表
mk_no part_no
-------- ----------
M01 TEST1
M02 TEST2
M03 TEST3
--想到的结果如下(仅限用SELECT)B.mk_no A.PART_NO A.PART_NAME A.J_NO PART_NAME
-------- -------- ---------- ------- ----------
M01 TEST1 NAME1 MJ1 J001
M02 TEST2 NAME2 MJ2 J002
M03 TEST3 NAME3 NULL NULL直接使用SELECT 加外链接可否实现?该如何实现?
insert into @A
select 'TEST1','NAME1','MJ1' union all
select 'TEST2','NAME2','MJ2' union all
select 'TEST3','NAME3',null union all
select 'MJ1','J001',null union all
select 'MJ2','J002',nulldeclare @B table(mk_no varchar(20),part_no varchar(20))
insert into @B
select 'M01','TEST1' union all
select 'M02','TEST2' union all
select 'M03','TEST3'select b2.*,a2.PART_NAME from @A a2 right join (
select b.*,a.PART_NAME,a.J_NO from @A a right join @B b on a.PART_NO=b.part_no
) b2 on a2.PART_NO=b2.J_NO/*result
mk_no part_no PART_NAME J_NO PART_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
M01 TEST1 NAME1 MJ1 J001
M02 TEST2 NAME2 MJ2 J002
M03 TEST3 NAME3 NULL NULL
*/
select b.mk_no,a.part_no,a.part_name.a.jo_n,c.part_name
from a,b,a as c
where b.part_no=a.part_no and a.jo_no*=c.part_no
B.mk_no A.PART_NO A.PART_NAME A.J_NO PART_NAME
-------- -------- ---------- ------- ----------
M01 TEST1 NAME1 MJ1 J001
M02 TEST2 NAME2 MJ2 J002
M03 TEST3 NAME3 NULL NULL