请问sql语句???(急急急) ----续1
表A 字段A1 字段A2
A001 A21
A002 A22
A003 A23
表B 字段B1 字段B2
A001 AC1
A001 AC1
c001 AC1
A002 AC2
A002 AC2
C002 AC2
表C 字段C1 字段C2
C001 C21
C002 C22
C003 C23
想得到
表AC 字段A2 字段C2
A21 C21
A22 C22
A23 nill
表的关系 表A的字段A1 一对多 表B的字段B1
表c的字段c1 一对一 表B的字段B1
通过表B的字段B2值相等, 表A和表c发生关联.
表A没有关系的，字段c2值为NILL。例：A23----NILL
表A的行数、字段A2的值和表AC 行数、字段A2的值相同
[解决办法]
Create Table A
(A1 varchar(10),A2 varchar(20))
insert into A
Select 'A001 ', 'A21 ' union all
Select 'A002 ', 'A22 ' union all
Select 'A003 ', 'A23 '
Create Table C
(C1 varchar(10),C2 varchar(20))
insert into c
Select 'C001 ', 'C21 ' union all
Select 'C002 ', 'C22 ' union all
Select 'C003 ', 'C23 '
Create Table B
(B1 varchar(10),B2 varchar(20))
insert into b
Select 'A001 ', 'AC1 ' union all
Select 'A001 ', 'AC1 ' union all
Select 'C001 ', 'AC1 ' union all
Select 'A002 ', 'AC2 ' union all
Select 'A002 ', 'AC2 ' union all
Select 'C002 ', 'AC2 '
Select * From (Select A2,B2 From A,B Where A1=B1) AA,(Select B2,C2 From B,C Where B1=C1) BB Where aa.B2=BB.B2
Select AA.A2,BB.C2 From
(Select Distinct A2,B2 From A Left join B ON A1=B1) AA
Left Join
(Select B2,C2 From B Inner join C On B1=C1) BB
On aa.B2=BB.B2
[解决办法]
环境：
--A
drop table #A
create table #A
(A1 varchar(50),
A2 varchar(50)
)
insert into #A
select 'A001 ', 'A21 ' union all select 'A002 ', 'A22 ' union all select 'A003 ', 'A23 '
select * from #A
--B
drop table #B
create table #B
(B1 varchar(50),
B2 varchar(50)
)
insert into #B
select 'A001 ', 'AC1 ' union all select 'A001 ', 'AC1 ' union all select 'c001 ', 'AC1 ' union all select 'A002 ', 'AC2 ' union all select 'A002 ', 'AC2 ' union all select 'C002 ', 'AC2 '
select * from #B
--C
drop table #C
create table #C
(C1 varchar(50),
C2 varchar(50)
)
insert into #C
select 'C001 ', 'C21 ' union all select 'C002 ', 'C22 ' union all select 'C003 ', 'C23 '
select * from #C
查询：
select t1.A2,isnull(t2.C2, 'nill ') C2 from
(select distinct #A.A2,#B.B2 from #A left join #B on #A.A1=#B.B1) t1
left join
(select distinct #C.C2,#B.B2 from #C left join #B on #C.C1=#B.B1 where B2 <> ' ') t2
on t1.B2=t2.B2
结果：
-----------------
A2 C2
A21 C21
A22 C22
A23 nill
[解决办法]
CREATE TABLE [A] (
[A1] varchar(20),
[A2] varchar(20)
)
CREATE TABLE [B] (
[B1] varchar(20),
[B2] varchar(20)
)
CREATE TABLE [C] (
[C1] varchar(20),
[C2] varchar(20)
)
INSERT INTO A
SELECT 'A001 ', 'A21 ' UNION ALL
SELECT 'A002 ', 'A22 ' UNION ALL
SELECT 'A003 ', 'A23 '
INSERT INTO B
SELECT 'A001 ', 'AC1 ' UNION ALL
SELECT 'A001 ', 'AC1 ' UNION ALL
SELECT 'C001 ', 'AC1 ' UNION ALL
SELECT 'A002 ', 'AC2 ' UNION ALL
SELECT 'A002 ', 'AC2 ' UNION ALL
SELECT 'C002 ', 'AC2 '
INSERT INTO C
SELECT 'C001 ', 'C21 ' UNION ALL
SELECT 'C002 ', 'C22 ' UNION ALL
SELECT 'C003 ', 'C23 '
SELECT AC.A2,BC.B2 FROM
(SELECT DISTINCT A.A2,B.B2 FROM A
LEFT OUTER JOIN B ON A.A1=B.B1) AC
LEFT OUTER JOIN
(
SELECT B.B2,C.C2 FROM B INNER JOIN C ON B.B1=C.C1
) BC ON AC.B2=BC.B2
drop table a
drop table b
drop table c
--结果
A2 B2
-------------------- --------------------
A21 AC1
A22 AC2
A23 NULL
(3 行受影响)
