三个表。表1为bill作用定单表,这里没有什么用,就不用列了
表2为bill_flight为航段表
表3为diary客人表
他们之间的关系为下一个定单可能有多个航段，多个客人,
现在表的结构如：现在下一个定单。二个航段。三个客人。入库为bill表一个记录,bill_flight二个记录,diary,三个记录
要求。数据合并到bill_flight表里，主要移过去的为client_autoid客人的id，和airno票号
create table bill_flight
(
flight_autoid int identity(1,1) primary key,/*行程的自动编号*/
bill_autoid int ,/*订单的时间编号(检索用)*/
filght_flightnumber varchar(50),/*航班号*/
filght_startcity varchar(50),/*出发城市*/
filght_endcity varchar(50),/*目的城市*/
filght_time varchar(50),/*起飞日期*/
filght_timeS varchar(50),/*起飞时间*/
filght_timeD varchar(50),/*起飞到达*/
client_autoid int,--人员id
airno varchar(50),--票号
)
create table diary
(
diary_autoid int identity(1,1) primary key,/*日志自动编号*/
bill_autoid int,--人员id
client_autoid varchar(50),/*客户的自动编号（检索用）．*/
airno varchar(50),/*票号*/
)
insert into bill_flight values('320','296','23','上海','石家庄','2007-01-17 16:04','4',null,'')
insert into bill_flight values('320','296','23','石家庄','黑龙江','2007-01-17 16:04','4',null,'')
insert into bill_flight values('320','296','23','黑龙江','上海','2007-01-17 16:04','4',null,'')
insert into bill_flight values('321','296','23','北京','成都','2007-01-17 16:04','4',null,'')
insert into bill_flight values('321','296','23','成都','北京','2007-01-17 16:04','4',null,'')
insert into diary values('320','351','479-6955460390')
insert into diary values('320','352','479-6955460391')
insert into diary values('320','353','479-6955460392')
insert into diary values('321','352','479-6955460391')
insert into diary values('321','353','479-6955460392')
delete from diary
delete from bill_flight
想要的结果如下bill_flight变成这个样子了
bill_autoid filght_flightnumber filght_startcity filght_endcity filght_time filght_timeS filght_timeD client_autoid airno
320 296 23 上海 石家庄 2007-01-17 16:04 4 351 479-6955460390
320 296 23 石家庄 黑龙江 2007-01-17 16:04 4 351 479-6955460390
320 296 23 黑龙江 上海 2007-01-17 16:04 4 351 479-6955460390
320 296 23 上海 石家庄 2007-01-17 16:04 4 352 479-6955460391
320 296 23 石家庄 黑龙江 2007-01-17 16:04 4 352 479-6955460391
320 296 23 黑龙江 上海 2007-01-17 16:04 4 352 479-6955460391
320 296 23 上海 石家庄 2007-01-17 16:04 4 353 479-6955460392
320 296 23 石家庄 黑龙江 2007-01-17 16:04 4 353 479-6955460392
320 296 23 黑龙江 上海 2007-01-17 16:04 4 353 479-6955460392
321 296 23 北京 成都 2007-01-17 16:04 4 352 479-6955460391
321 296 23 成都 北京 2007-01-17 16:04 4 352 479-6955460391
321 296 23 北京 成都 2007-01-17 16:04 4 353 479-6955460392
321 296 23 成都 北京 2007-01-17 16:04 4 353 479-6955460392
------解决方案--------------------
是这样吗
- SQL code
select a.flight_autoid,a.bill_autoid,a.filght_flightnumber,a.filght_startcity,a.filght_endcity,a.filght_time,a.filght_timeS,b.client_autoid,b.airno from bill_flight a,diary b where a.bill_autoid=b.bill_autoid/*flight_autoid bill_autoid filght_flightnumber filght_startcity filght_endcity filght_time filght_timeS client_autoid airno------------- ----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------1 320 296 23 上海 石家庄 2007-01-17 16:04 351 479-6955460390 2 320 296 23 石家庄 黑龙江 2007-01-17 16:04 351 479-6955460390 3 320 296 23 黑龙江 上海 2007-01-17 16:04 351 479-6955460390 1 320 296 23 上海 石家庄 2007-01-17 16:04 352 479-6955460391 2 320 296 23 石家庄 黑龙江 2007-01-17 16:04 352 479-6955460391 3 320 296 23 黑龙江 上海 2007-01-17 16:04 352 479-6955460391 1 320 296 23 上海 石家庄 2007-01-17 16:04 353 479-6955460392 2 320 296 23 石家庄 黑龙江 2007-01-17 16:04 353 479-6955460392 3 320 296 23 黑龙江 上海 2007-01-17 16:04 353 479-6955460392 4 321 296 23 北京 成都 2007-01-17 16:04 352 479-6955460391 5 321 296 23 成都 北京 2007-01-17 16:04 352 479-6955460391 4 321 296 23 北京 成都 2007-01-17 16:04 353 479-6955460392 5 321 296 23 成都 北京 2007-01-17 16:04 353 479-6955460392 (13 row(s) affected)*/