请问sql语句???(急急急)
表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 C23
表的关系 表A的字段A1 一对多 表B的字段B1
表c的字段c1 一对一 表B的字段B1
通过表B的字段B2值相等, 表A和表c发生关联.
[解决办法]
declare @a table(A1 varchar(10),A2 varchar(20))
insert @a select 'A001 ', 'A21 '
union all select 'A002 ', 'A22 '
union all select 'A003 ', 'A23 '
declare @b table(b1 varchar(10),b2 varchar(10))
insert @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 '
declare @c table(C1 varchar(10),C2 varchar(10))
insert @c select 'C001 ', 'C21 '
union all select 'C002 ', 'C22 '
union all select 'C003 ', 'C23 '
select a2,c2 from (
select * from @a a Inner Join
(select distinct b.b1,c.b1 bb,c.b2 from @b b Inner Join @b c on b.b2=c.b2 and b.b1 <> c.b1) d on a.a1=d.b1)
e Inner Join @c cc on e.bb=cc.c1
[解决办法]
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.C2 FROM
(SELECT C.*,B.B2 FROM C INNER JOIN B ON B.B1=C.C1 ) BC
INNER JOIN
(SELECT DISTINCT A.*,B.B2 FROM A INNER JOIN B ON A.A1=B.B1 ) AC
ON BC.B2=AC.B2
drop table a
drop table b
--结果
A2 C2
-------------------- --------------------
A21 C21
A22 C22
(2 行受影响)
drop table C
[解决办法]
select a2,c2 from (
select * from @a a Inner Join
(select distinct b.b1,c.b1 bb,c.b2 from @b b Inner Join @b c on b.b2=c.b2 and b.b1 <> c.b1) d on a.a1=d.b1)
e Inner Join @c cc on e.bb=cc.c1
[解决办法]
select 表A.字段A1 , t.字段C2 from 表A,
(select 表B.字段B1 , 表C.字段C2 from 表B,表C where 表B.字段B1 = 表C.字段C2) t
where 表A.字段A1 = t.字段B1
[解决办法]
select
distinct a.字段A2,d.字段C2
from
表A a,
表B b,
表B c,
表C d
where
a.字段A1=b.字段B1
and
b.字段B2=c.字段B2
and
c.字段B1=d.字段C1
