求一SQL查询语句!多谢~
求一SQL查询语句!谢谢~!
A表:
A_ID BK_NO NAME
1 001 A
2 002 B
3 003 C
B表:
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
------解决方案--------------------
Full Join
------解决方案--------------------
SELECT
(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
------解决方案--------------------
SELECT A.*, B.*
FROM B FULL OUTER JOIN A ON A.BK_NO = B.BK_NO
------解决方案--------------------
Declare @A Table(A_ID Int,BK_NO Varchar(10),Name Char)
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
------解决方案--------------------
select a.*,b.* from a full join b on a.BK_NO =b.BK_NO
------解决方案--------------------
“也就是说:先从A表和B表查出结果集,再结合B表BK_NO为空的条件,得出一个结果集;不知道明不明白我的意思!”
SELECT A.*, B.*
FROM B FULL OUTER JOIN
A ON A.BK_NO = B.BK_NO
WHERE (A.NAME IS NULL) OR
(A.NAME = ' ') OR
A表:
A_ID BK_NO NAME
1 001 A
2 002 B
3 003 C
B表:
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
------解决方案--------------------
Full Join
------解决方案--------------------
SELECT
(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
------解决方案--------------------
SELECT A.*, B.*
FROM B FULL OUTER JOIN A ON A.BK_NO = B.BK_NO
------解决方案--------------------
Declare @A Table(A_ID Int,BK_NO Varchar(10),Name Char)
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
------解决方案--------------------
select a.*,b.* from a full join b on a.BK_NO =b.BK_NO
------解决方案--------------------
“也就是说:先从A表和B表查出结果集,再结合B表BK_NO为空的条件,得出一个结果集;不知道明不明白我的意思!”
SELECT A.*, B.*
FROM B FULL OUTER JOIN
A ON A.BK_NO = B.BK_NO
WHERE (A.NAME IS NULL) OR
(A.NAME = ' ') OR