＋设为首页 ＋添加到收藏夹
高分求助一个比较烦人的sql合并数据的语句
两个表的内容我想合并一下
table1
c_id start_place end_place
c1 m1 m2
c2 m2 m3
c3 m3 m4
c4 m3 m5
table2
m_id long width
m1 1 2
m2 2 3
m3 2 5
m4 3 6
m5 4 7
合并为一个表table3
c_id start_place_long start_place_width end_place_long end_place_width
c1 1 2 2 3
c2 2 3 2 5
c3 2 5 3 6
c4 2 5 4 7
写错了，应该是：
select a.c_id, b.long start_place_long, b.width start_place_width, c.long end_place_long, c.width end_place_width from table1 a LEFT OUTER JOIN table2 b on a.start_place=b.m_id LEFT OUTER JOIN table2 c on a.end_place=c.m_id
select a.c_id,b.long as start_place_long,b.width as start_place_width,c.long as end_place_long,c.width as end_place_width from table1 a inner join table2 on a.start_place=b.m_id inner join table2 c on a.end_place=c.m_id
select a.c_id,b.long start_place_long,b.width start_place_width
c.long end_place_long,c.width end_place_width
from table1 a join table2 b on a.start_place=b. m_id
join table2 c on a.end_place=c.m_id
select a.c_id,b.long [start_place_long],b.width [start_place_width],c.long [end_place_long],c.width [end_place_width] from table1 a left join table2 b on a.start_place=b.m_id left join table2 c on a.end_place=b.m_id
select a.c_id,b.long [start_place_long],b.width [start_place_width],c.long [end_place_long],c.width [end_place_width] from table1 a
left join table2 b on a.startplace=b.m_id
left join table2 c on a.endplace=c.m_id
如果要把查询得到的值插入到table3中，只需修改SQL语句，用批插入。
1.假设table3表不存在：
select a.c_id,b.long as start_place_long,b.width
as start_place_width,c.long as end_place_long,
c.width as end_place_width
into table3
from table1 a inner join table2 b
on a.start_place=b.m_id
inner join table2 c on a.end_place=c.m_id;
2.假设table3表已存在：
insert into table3
select a.c_id,b.long ,b.width ,c.long ,
c.width
from table1 a inner join table2 b
on a.start_place=b.m_id
inner join table2 c on a.end_place=c.m_id;
cmd:=insert table3 select a.c_id,b.long start_place_long,b.width
+start_place_width,c.long end_place_long,c.width end_place_width from
+table1 a
+ inner join table2 b on a.start_place=b.m_id
+ inner join table2 c on a.end_place=c.m_id;
adoQuery2.SQL.Clear;
adoQuery2.SQL.Add(Cmd);
adoQuery2.execsql;
edit1.text:=inttostr(adoquery2.recordcount);
cmd:=insert table3 select a.c_id,b.long start_place_long,b.width
+start_place_width,c.long end_place_long,c.width end_place_width from
+table1 a
+ inner join table2 b on a.start_place=b.m_id
+ inner join table2 c on a.end_place=c.m_id;
adoQuery2.SQL.Clear;
adoQuery2.SQL.Add(+Cmd+);
adoQuery2.execsql;
