三个表的简单查询,烦请大家给指点一下
三个表的简单查询
A表
A1
A2
A3
B表
B1
B2
B3
B4
C表
A1 B1 C1
A1 B3 C2
......
要求得到一个总表：
A1 B1 C1
A1 B2 NULL
A1 B3 C1
A1 B4 NULL
A2 B1 NULL
A2 B2 NULL
A2 B3 NULL
A2 B4 NULL
A3 B1 NULL
A3 B2 NULL
A3 B3 NULL
A3 B4 NULL
这样的SQL怎么去写？烦请大家给指点一下，谢谢
[解决办法]
Create Table A
(aVarchar(10),
bVarchar(10),
cVarchar(10))
Create Table B
(bVarchar(10),
dVarchar(10),
eVarchar(10))
Create Table C
(aVarchar(10),
dVarchar(10),
fVarchar(10))
Insert A Select 'a1 ', 'b1 ', 'c1 '
Union All Select 'a2 ', 'b2 ', 'c2 '
Union All Select 'a3 ', 'b1 ', 'c3 '
Insert B Select 'b1 ', 'd1 ', 'e1 '
Union All Select 'b1 ', 'd2 ', 'e2 '
Union All Select 'b1 ', 'd3 ', 'e3 '
Union All Select 'b2 ', 'd2 ', 'e4 '
Insert C Select 'a1 ', 'd1 ', 'f1 '
GO
Create View V_TEST
As
Select
TOP 100 Percent
A.a,
A.b,
A.c,
B.d,
B.e,
C.f
From
A
Inner Join
B
On A.b = B.b
Left Join
C
On A.a = C.a And B.d = C.d
Order By A.a, A.b, A.c
GO
Select * From V_TEST O
GO
Drop Table A, B, C
Drop View V_TEST
--Result
/*
abcdef
a1b1c1d1e1f1
a1b1c1d2e2NULL
a1b1c1d3e3NULL
a2b2c2d2e4NULL
a3b1c3d1e1NULL
a3b1c3d2e2NULL
a3b1c3d3e3NULL
*/
