求一个sql语句,问题如下,麻烦一下大虾:
(主)表A:item_number, line, docid
cpl1 1 n001
cpl2 3 n002
cpl3 2 n003(从)表B: item_A name docid
A1 n1 n001
B1 n2 n001
C1 n3 n001
A2 M1 n002
B2 M2 n002
E3 p1 n003
F3 NE2 n003表C :docid des item_number
c01 d1 cpl1
c02 d2 cpl2
c03 d3 cpl3
创建的表D: docid name item_A
c01 n1 A1
c01 n2 B1
c01 n3 C1
c02 M1 A2
c02 m2 B2
c03 p1 E3
c03 d3 F3
刚刚我自己的题目写错误了。意思是这样的,我想创建一个表D,数据是从表B和表C中提取,其中表B和表c没有直接的联系,表A联系的,表A是表B的主表。
这个sql语句如何写。谢谢了。
(主)表A:item_number, line, docid
cpl1 1 n001
cpl2 3 n002
cpl3 2 n003(从)表B: item_A name docid
A1 n1 n001
B1 n2 n001
C1 n3 n001
A2 M1 n002
B2 M2 n002
E3 p1 n003
F3 NE2 n003表C :docid des item_number
c01 d1 cpl1
c02 d2 cpl2
c03 d3 cpl3
创建的表D: docid name item_A
c01 n1 A1
c01 n2 B1
c01 n3 C1
c02 M1 A2
c02 m2 B2
c03 p1 E3
c03 d3 F3
刚刚我自己的题目写错误了。意思是这样的,我想创建一个表D,数据是从表B和表C中提取,其中表B和表c没有直接的联系,表A联系的,表A是表B的主表。
这个sql语句如何写。谢谢了。
from A,B,C
where A.docid=B.docid and A.item_numer=C.item_number
FROM A,B,C WHERE A.item_number=B.item_number AND A.
item_number=C.item_number?
C.docid,B.name,B.item_A
from
A,B,C
where
A.docid=B.docid
and
A.item_numer=C.item_number
docid=c.docid,
name=b.name,
c.item_number
from c join a on c.item_number=a.item_number
join b on b.docid=c.docid
docid=c.docid,
name=b.name,
item_a
from c join a on c.item_number=a.item_number
join b on b.docid=c.docid
inner join a on a.item_number=c.item_number
inner join b on a.docid=b.docid
IF OBJECT_ID('table1') IS NOT NULL
DROP TABLE table1
GO
CREATE TABLE table1( item_number varchar(12), line int, docid varchar(12))
goIF OBJECT_ID('table2') IS NOT NULL
DROP TABLE table2
GO
CREATE TABLE table2( item_A varchar(12), [name] varchar(12), docid varchar(12) )
goIF OBJECT_ID('table3') IS NOT NULL
DROP TABLE table3
GO
CREATE TABLE table3( docid varchar(12), des varchar(12), item_number varchar(12))
go
insert table1 select
'cpl1' , 1 , 'n001' union all select
'cpl2' , 3 , 'n002' union all select
'cpl3', 2 , 'n003' insert table2 select
'A1' , 'n1' ,'n001' union all select
'B1' , 'n2' ,'n001' union all select
'C1' , 'n3' , 'n001' union all select
'A2' , 'M1' , 'n002' union all select
'B2' , 'M2' , 'n002' union all select
'E3' , 'p1' , 'n003' union all select
'F3' , 'NE2', 'n003' insert table3 select
'c01' , 'd1' , 'cpl1' union all select
'c02' , 'd2' , 'cpl2' union all select
'c03' , 'd3' , 'cpl3'
select c.docid,[name],b.item_A from table1 a
join table2 b on a.docid =b.docid
join table3 c on a.item_number=c.item_numberdocid name item_A
------------ ------------ ------------
c01 n1 A1
c01 n2 B1
c01 n3 C1
c02 M1 A2
c02 M2 B2
c03 p1 E3
c03 NE2 F3(7 行受影响)
insert into a select 'cpl1', 1, 'n001'
union all select 'cpl2', 3, 'n002'
union all select 'cpl3', 2 , 'n003'
g0create table b (item_A varchar(10), [name] varchar(10), docid varchar(10))
insert into b select 'A1', 'n1', 'n001'
union all select 'B1' , 'n2', 'n001'
union all select 'C1' , 'n3' , 'n001'
union all select 'A2' , 'M1' , 'n002'
union all select 'B2' , 'M2' , 'n002'
union all select 'E3' , 'p1' , 'n003'
union all select 'F3' , 'NE2' , 'n003'
gocreate table c (docid varchar(10), [des] varchar(10), item_number varchar(10) )
insert into c select 'c01' , 'd1' , 'cpl1'
union all select 'c02', 'd2' , 'cpl2'
union all select'c03' , 'd3' , 'cpl3'
goselect a.docid,b.[name],b.item_A from a
inner join b on a.docid=b.docid
inner join c on a.item_number=c.item_number/*docid name item_A
---------- ---------- ----------
n001 n1 A1
n001 n2 B1
n001 n3 C1
n002 M1 A2
n002 M2 B2
n003 p1 E3
n003 NE2 F3(7 行受影响)*/
from a,b,c
where a.docid=b.docid and a.item_number=c.item_number and b.docid=c.docid