A表:
A_ID BK_NO NAME
1 001 A
2 002 B
3 003 CB表:
B_ID BK_NO CHG_CODE
1 001 AAA
2 002 BBB
3 003 CCC
4 DDD
5 EEE
6 001 FFF怎样能查出如下:
A_ID BK_NO NAME B_ID BK_NO CHG_CODE
1 001 A 1 001 AAA
1 001 A 6 001 FFF
2 002 B 2 002 BBB
3 003 C 3 003 CCC
4 DDD
5 EEE
A_ID BK_NO NAME
1 001 A
2 002 B
3 003 CB表:
B_ID BK_NO CHG_CODE
1 001 AAA
2 002 BBB
3 003 CCC
4 DDD
5 EEE
6 001 FFF怎样能查出如下:
A_ID BK_NO NAME B_ID BK_NO CHG_CODE
1 001 A 1 001 AAA
1 001 A 6 001 FFF
2 002 B 2 002 BBB
3 003 C 3 003 CCC
4 DDD
5 EEE
(SELECT A.A_ID FROM A WHERE A.BK_NO = B.BK_NO) AS A_ID,
(SELECT A.NAME FROM A WHERE A.BK_NO = B.BK_NO) AS NAME,
B.*
FROM B
FROM B FULL OUTER JOIN A ON A.BK_NO = B.BK_NO
Insert @A Select 1 ,'001','A'
Union All Select 2,'002','B'
Union All Select 3,'003','C'
--Declare @B Table(B_ID Int,BK_No Varchar(10),CHG_CODE Varchar(10))
Insert @B Select 1,'001','AAA'
Union all Select 2,'002','BBB'
Union all Select 3,'003','CCC'
Union all Select 4,'','DDD'
Union all Select 5,'','EEE'
Union all Select 6,'001','FFF'
SELECT A.*, B.*
FROM @B B FULL OUTER JOIN @A A ON A.BK_NO = B.BK_NO
A表按条件呢?就不对了比如A表按条件:NAME为A的,
查出要如下结果:
A_ID BK_NO NAME B_ID BK_NO CHG_CODE
1 001 A 1 001 AAA
1 001 A 6 001 FFF
4 DDD
5 EEE
这个语句怎么写呢?
FROM B FULL OUTER JOIN
A ON A.BK_NO = B.BK_NO
WHERE (A.NAME IS NULL) OR
(A.NAME = '') OR
(A.NAME = 'A')
FROM B FULL OUTER JOIN A ON A.BK_NO = B.BK_NO order by bk_no