三个表的简单查询A表
A1
A2
A3B表
B1
B2
B3
B4C表
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怎么去写?烦请大家给指点一下,谢谢
A1
A2
A3B表
B1
B2
B3
B4C表
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怎么去写?烦请大家给指点一下,谢谢
select d.col1,d.col2 from (select a.col as col1,b.col as col2 from a full join b) d left join c on d.col1=c.col1 and d.col2=c.col2
select d.col1,d.col2,c.col3 from (select a.col as col1,b.col as col2 from a full join b) d left join c on d.col1=c.col1 and d.col2=c.col2
from
(select a.col as col1,b.col as col2 from a,b) d,c
where
d.col1=c.col1(+) and d.col2=c.col2(+)
select c.col1,c.col2,d.col3
from
(select a.col1 col1,b.col1 col2 from 表A a,表B b order by a.col1) c
left join 表C d
on c.col1=d.col1 and c.col2=d.col2
我看差了,还以为是oracle呢;请取消我的回答,向楼主、userqin 道歉!
select d.col1,d.col2,c.col3 from (select a.col as col1,b.col as col2 from 表a,表b) d left join 表c c on d.col1=c.col1 and d.col2=c.col2
多谢licsth(北京三毛)提醒
A.A,
B.B,
C.C
From
A
Cross Join
B
Left Join
C
On A.A = C.A And B.B = C.B
(A Varchar(10))
Insert A Select 'A1'
Union All Select 'A2'
Union All Select 'A3'Create Table B
(B Varchar(10))
Insert B Select 'B1'
Union All Select 'B2'
Union All Select 'B3'
Union All Select 'B4'Create Table C
(A Varchar(10),
B Varchar(10),
C Varchar(10))
Insert C Select 'A1', 'B1', 'C1'
Union All Select 'A1', 'B3', 'C2'
GO
Select
A.A,
B.B,
C.C
From
A
Cross Join
B
Left Join
C
On A.A = C.A And B.B = C.B
GO
Drop Table A, B ,C
--Result
/*
A B C
A1 B1 C1
A1 B2 NULL
A1 B3 C2
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
*/
没看出来
A1 B1 C1
A1 B2 NULL
A1 B3 C1 ----这条是A1 B3 C2 吧
A.A,
B.B,
C.C
From
A
Cross Join
B
Left Join
C
On A.A = C.A And B.B = C.B-------------------------
说的对 用交错连接 没什么好说的 A表和B 表 交错连接 然后和C 表自然连接
有三个表 A B C
A中有三个字段 A.a A.b A.c
B中有三个字段 B.b B.d B.e
C中有三个字段 C.a C.d C.f希望能够得到下面这样的view
1).A表和B表结合。A表和B表是父子关系。
2).A表和B表结合后,将C表中的内容再填入其中。
A.a A.b A.c B.d B.e C.f若
A表中数据为:
a1 b1 c1
a2 b2 c2
a3 b1 c3B表中数据为:
b1 d1 e1
b1 d2 e2
b1 d3 e3
b2 d2 e4C表中的数据为:
a1 d1 f1得到的VIEW为:
a1 b1 c1 d1 e1 f1
a1 b1 c1 d2 e2 null
a1 b1 c1 d3 e3 null
a2 b2 c2 d2 e2 null
a3 b1 c3 d1 e1 null
a3 b1 c3 d2 e2 null
a3 b1 c3 d3 e3 null
As
Select
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
GO
Select * From V_TEST
(a Varchar(10),
b Varchar(10),
c Varchar(10))Create Table B
(b Varchar(10),
d Varchar(10),
e Varchar(10))Create Table C
(a Varchar(10),
d Varchar(10),
f Varchar(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
/*
a b c d e f
a1 b1 c1 d1 e1 f1
a1 b1 c1 d2 e2 NULL
a1 b1 c1 d3 e3 NULL
a2 b2 c2 d2 e4 NULL
a3 b1 c3 d1 e1 NULL
a3 b1 c3 d2 e2 NULL
a3 b1 c3 d3 e3 NULL
*/
大师级指点。