日期:2014-5-18 浏览次数:20051次 点赞次数:20

高手帮写一条sql语句吧实在写不出来了
三个表。表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
------------- ----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------------