以下表中的主键是联合主键,并且主键相同,怎样把他们合并主键为:ID,TIME表1:ID TIME FIELD1 FIELD2
1 2000-1-1 11 21
2 2001-1-1 12 22
3 2002-1-1 13 23
4 2003-1-1 14 24 表2:ID TIME FIELD3 FIELD4
1 2000-1-1 31 41
2 2001-1-1 32 42
3 2002-1-1 33 43
4 2003-1-1 34 44表3:ID TIME FIELD5 FIELD6
1 2000-1-1 51 61
2 2001-1-1 52 62
3 2002-1-1 53 63第三个表没有(2003-1-1 34)这个主键
合并后的表:
ID TIME FIELD1 FIELD2 FIELD3 FIELD4 FIELD5 FIELD6
1 2000-1-1 11 21 31 41 51 61
2 2001-1-1 12 22 32 42 52 62
3 2002-1-1 13 23 33 43 53 63
1 2000-1-1 11 21
2 2001-1-1 12 22
3 2002-1-1 13 23
4 2003-1-1 14 24 表2:ID TIME FIELD3 FIELD4
1 2000-1-1 31 41
2 2001-1-1 32 42
3 2002-1-1 33 43
4 2003-1-1 34 44表3:ID TIME FIELD5 FIELD6
1 2000-1-1 51 61
2 2001-1-1 52 62
3 2002-1-1 53 63第三个表没有(2003-1-1 34)这个主键
合并后的表:
ID TIME FIELD1 FIELD2 FIELD3 FIELD4 FIELD5 FIELD6
1 2000-1-1 11 21 31 41 51 61
2 2001-1-1 12 22 32 42 52 62
3 2002-1-1 13 23 33 43 53 63
select a.* , b.field3 , b.field4 , c.field5 , c.field6
from 表1 a , 表2 b , 表3 c
where a.id = b.id and a.id = c.id and a.time = b.time and a.time = c.time
圈 地网
-----------
select a.*,b.filed3,b.filed4,c.filed5,c.filed6 from t1 a,t2 b,t3 c where a.time=b.time and b.time=c.time
drop table 表1
go
create table 表1
(
ID int,
TIME varchar(10),
FIELD1 int,
FIELD2 int
)
insert into 表1(ID,TIME,FIELD1,FIELD2) values(1, '2000-1-1', 11, 21)
insert into 表1(ID,TIME,FIELD1,FIELD2) values(2, '2001-1-1', 12, 22)
insert into 表1(ID,TIME,FIELD1,FIELD2) values(3, '2002-1-1', 13, 23)
insert into 表1(ID,TIME,FIELD1,FIELD2) values(4, '2003-1-1', 14, 24)if object_id('pubs..表2') is not null
drop table 表2
go
create table 表2
(
ID int,
TIME varchar(10),
FIELD3 int,
FIELD4 int
)
insert into 表2(ID,TIME,FIELD3,FIELD4) values(1, '2000-1-1', 31, 41)
insert into 表2(ID,TIME,FIELD3,FIELD4) values(2, '2001-1-1', 32, 42)
insert into 表2(ID,TIME,FIELD3,FIELD4) values(3, '2002-1-1', 33, 43)
insert into 表2(ID,TIME,FIELD3,FIELD4) values(4, '2003-1-1', 34, 44)if object_id('pubs..表3') is not null
drop table 表3
go
create table 表3
(
ID int,
TIME varchar(10),
FIELD5 int,
FIELD6 int
)
insert into 表3(ID,TIME,FIELD5,FIELD6) values(1, '2000-1-1', 51, 61)
insert into 表3(ID,TIME,FIELD5,FIELD6) values(2, '2001-1-1', 52, 62)
insert into 表3(ID,TIME,FIELD5,FIELD6) values(3, '2002-1-1', 53, 63)select a.* , b.field3 , b.field4 , c.field5 , c.field6
from 表1 a , 表2 b , 表3 c
where a.id = b.id and a.id = c.id and a.time = b.time and a.time = c.timedrop table 表1,表2,表3ID TIME FIELD1 FIELD2 field3 field4 field5 field6
----------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
1 2000-1-1 11 21 31 41 51 61
2 2001-1-1 12 22 32 42 52 62
3 2002-1-1 13 23 33 43 53 63(所影响的行数为 3 行)
-- ---------- ----------- ----------- ----------- ----------- ----------- ------
1 2000-1-1 11 21 31 41 51 61
2 2001-1-1 12 22 32 42 52 62
3 2002-1-1 13 23 33 43 53 63(所影响的行数为 3 行)
insert T1 select 1, '2000-1-1', 11, 21
union all select 2, '2001-1-1', 12, 22
union all select 3, '2002-1-1', 13, 23
union all select 4, '2003-1-1', 14, 24 create table T2(ID int, TIME datetime, FIELD3 int, FIELD4 int)
insert T2 select 1, '2000-1-1', 31, 41
union all select 2, '2001-1-1', 32, 42
union all select 3, '2002-1-1', 33, 43
union all select 4, '2003-1-1', 34, 44create table T3(ID int, TIME datetime, FIELD5 int, FIELD6 int)
insert T3 select 1, '2000-1-1', 51, 61
union all select 2, '2001-1-1', 52, 62
union all select 3, '2002-1-1', 53, 63select T1.*, T2.FIELD3, T2.FIELD4, T3.FIELD5, T3.FIELD6 from T1, T2, T3
where T1.ID=T2.ID and T1.Time=T2.Time and T1.ID=T3.ID and T1.Time=T3.Time
--result
ID TIME FIELD1 FIELD2 FIELD3 FIELD4 FIELD5 FIELD6
----------- ------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- -----------
1 2000-01-01 00:00:00.000 11 21 31 41 51 61
2 2001-01-01 00:00:00.000 12 22 32 42 52 62
3 2002-01-01 00:00:00.000 13 23 33 43 53 63(3 row(s) affected)