高手帮写一条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
32029623上海石家庄2007-01-17 16:044 351 479-6955460390
32029623石家庄黑龙江2007-01-17 16:044 351 479-6955460390
32029623黑龙江上海2007-01-17 16:044 351 479-6955460390
32029623上海石家庄2007-01-17 16:044 352 479-6955460391
32029623石家庄黑龙江2007-01-17 16:044 352 479-6955460391
32029623黑龙江上海2007-01-17 16:044 352 479-6955460391
32029623上海石家庄2007-01-17 16:044 353 479-6955460392
32029623石家庄黑龙江2007-01-17 16:044 353 479-6955460392
32029623黑龙江上海2007-01-17 16:044 353 479-6955460392
32129623北京成都2007-01-17 16:044 352 479-6955460391
32129623成都北京2007-01-17 16:044 352 479-6955460391
32129623北京成都2007-01-17 16:044 353 479-6955460392
32129623成都北京2007-01-17 16:044 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)*/
[解决办法]
- SQL code
这样？select a.bill_autoid,a.filght_flightnumber,a.filght_startcity,a.filght_endcity,a.filght_time,a.filght_timeS,a.filght_timeD,b.client_autoid,b.airnofrom bill_flight a,diary b where a.bill_autoid=b.bill_autoid
[解决办法]
- SQL code
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 ') goselect a.bill_autoid,a.filght_flightnumber,a.filght_startcity,a.filght_endcity,a.filght_time,a.filght_timeS,a.filght_timeD , b.client_autoid,b.airno from bill_flight a,diary bwhere a.bill_autoid = b.bill_autoiddrop table diary drop table 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 （所影响的行数为 13 行）*/
[解决办法]
- SQL code
select b.bill_autoid, b.filght_flightnumber, b.filght_startcity, b.filght_endcity, b.filght_time, b.filght_timeS, b.filght_timeD, a.bill_autoid, a.client_autoid, a.airnofrom diary ajoin bill_flight b on a.bill_autoid=b.bill_autoidorder by b.bill_autoid,a.client_autoid asc
[解决办法]
- SQL code
--如果是更新的话,把查询结果放一临时表,然后清空bill_flight表,最后从临时表中将数据插入bill_flight表.
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 ')
go
select a.bill_autoid,a.filght_flightnumber,a.filght_startcity,a.filght_endcity,a.filght_time,a.filght_timeS,a.filght_timeD ,
b.client_autoid,b.airno
into tmp
from bill_flight a,diary b
where a.bill_autoid = b.bill_autoid
delete from bill_flight
insert into bill_flight select * from tmp
select * from bill_flight
--drop table diary ,tmp , 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
（所影响的行数为 13 行）
*/