请教一条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
谢谢~
[解决办法]
select a.*,b.* from a full join b on a.bk_no=b.bk_no
[解决办法]
select t1.*, t2.* from A表 t1 right join B表 t2 on t1.BK_NO = t2.BK_NO
[解决办法]
D版已经答过了~
Full Join
[解决办法]
select t1.*, t2.* from A表 t1 right join B表 t2 on t1.BK_NO = t2.BK_NO where t1.[name] = 'A ' or t2.BK_NO is null
[解决办法]
以B表为主表不就行了
[解决办法]
select a.*,b.* from B表 b
left join A表 a
on a.bk_no=b.bk_no
[解决办法]
Create Table A
(A_IDInt,
BK_NOChar(3),
NAMEVarchar(10))
Insert A Select 1, '001 ', 'A '
Union All Select 2, '002 ', 'B '
Union All Select 3, '003 ', 'C '
Create Table B
(B_IDInt,
BK_NOChar(3),
CHG_CODEVarchar(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 '
GO
--得到所有的
Select
A.*, B.*
From A
Full Join B
On A.BK_NO = B.BK_NO
Order By (IsNull(A.A_ID, 1000000)), B_ID
--得到NAME为A的
Select
A.*, B.*
From A
Full Join B
On A.BK_NO = B.BK_NO
Where A.NAME = 'A ' Or A.NAME Is Null
Order By (IsNull(A.A_ID, 1000000)), B_ID
Go
Drop Table A,B
----Result
/*
A_IDBK_NONAMEB_IDBK_NOCHG_CODE
1001A1001AAA
1001A6001FFF
2002B2002BBB
3003C3003CCC
NULLNULLNULL4DDD
NULLNULLNULL5EEE
A_IDBK_NONAMEB_IDBK_NOCHG_CODE
1001A1001AAA
1001A6001FFF
NULLNULLNULL4DDD
NULLNULLNULL5EEE
*/
