假如存在两张表,
表A 的字段:Flt_Nbr 航班号,Flt_Rte_Cd 航班航线,Dpt_Airpt_Cd 出发机场,Arrv_Airpt_Cd 到达机场
表B的字段:Dpt_Airpt_Cd 出发机场,Arrv_Airpt_Cd 到达机场,Airpt_Dstn 距离
问题:根据航班求两个地点的距离?注:1. 一个航班号对应着一个航班航线
2. 表A和表B中的字段均为char类型
3. 航班航线有两种格式,一种如PEK_PVG 北京-上海,出发机场为北京,到达机场为上海。
还有一种如 PEK_CKG_PVG 北京-重庆-上海, 出发机场为北京,中转机场为重庆,到达机场为上海
现在让根据航班计算出发地点和到达地点的距离
表A 的字段:Flt_Nbr 航班号,Flt_Rte_Cd 航班航线,Dpt_Airpt_Cd 出发机场,Arrv_Airpt_Cd 到达机场
表B的字段:Dpt_Airpt_Cd 出发机场,Arrv_Airpt_Cd 到达机场,Airpt_Dstn 距离
问题:根据航班求两个地点的距离?注:1. 一个航班号对应着一个航班航线
2. 表A和表B中的字段均为char类型
3. 航班航线有两种格式,一种如PEK_PVG 北京-上海,出发机场为北京,到达机场为上海。
还有一种如 PEK_CKG_PVG 北京-重庆-上海, 出发机场为北京,中转机场为重庆,到达机场为上海
现在让根据航班计算出发地点和到达地点的距离
航班航线是指从哪里到哪里,中间可能有中转
由于数据实在庞大就找几个:
表A:Flt_Nbr 航班号, Flt_Rte_Cd 航班航线, Dpt_Airpt_Cd 出发机场, Arrv_Airpt_Cd 到达机场
8023 CKG-CTU CKG CTU
8045 PEK-CKG PEK PEK
8078 CKG-NKG-HRB CKG HRB
8056 HRB-CKG-NKG HRB NKG表B:Dpt_Airpt_Cd 出发机场,Arrv_Airpt_Cd 到达机场,Airpt_Dstn 距离
CKG CTU 500
PEK CKG 600
CKG NKG 510
NKG HRB 360
表A 的字段:Flt_Nbr 航班号,Flt_Rte_Cd 航班航线,Dpt_Airpt_Cd 出发机场,Arrv_Airpt_Cd 到达机场
表B的字段:Dpt_Airpt_Cd 出发机场,Arrv_Airpt_Cd 到达机场,Airpt_Dstn 距离
问题:根据航班求两个地点的距离? 注:1. 一个航班号对应着一个航班航线
2. 表A和表B中的字段均为char类型
3. 航班航线有两种格式,一种如PEK_PVG 北京-上海,出发机场为北京,到达机场为上海。
还有一种如 PEK_CKG_PVG 北京-重庆-上海, 出发机场为北京,中转机场为重庆,到达机场为上海
现在让根据航班计算出发地点和到达地点的距离
因为数据庞大只找几个
例如
表A:Flt_Nbr 航班号, Flt_Rte_Cd 航班航线, Dpt_Airpt_Cd 出发机场, Arrv_Airpt_Cd 到达机场
8023 CKG-CTU CKG CTU
8045 PEK-CKG PEK PEK
8078 CKG-NKG-HRB CKG HRB
8056 HRB-CKG-NKG HRB NKG 表B:Dpt_Airpt_Cd 出发机场,Arrv_Airpt_Cd 到达机场,Airpt_Dstn 距离
CKG CTU 500
PEK CKG 600
CKG NKG 510
NKG HRB 360
CREATE TABLE a
(
Flt_Nbr char(20),
Flt_Rte_Cd varchar(50),
Dpt_Airpt_Cd varchar(10),
Arrv_Airpt_Cd varchar(10)
)
create table b
(
Dpt_Airpt_Cd varchar(10),
Arrv_Airpt_Cd varchar(10),
Airpt_Dstn int
)
insert into a values ('8023','CKG-CTU' , 'CKG','CTU')
insert into a values ('8045','PEK-CKG' , 'PEK','CKG')
insert into a values ('8078','CKG-NKG-HRB', 'CKG','HRB')
insert into a values ('8056','HRB-CKG-NKG', 'HRB','NKG')
insert into b values ('CKG' ,'CTU',500)
insert into b values ('PEK' ,'CKG',600 )
insert into b values ('CKG' ,'NKG',510 )
insert into b values ('NKG' ,'HRB',360 )
select c.Dpt_Airpt_Cd,
c.Arrv_Airpt_Cd,
ISNULL(c.Airpt_Dstn + d.Airpt_Dstn,0) as Airpt_Dstn
from ( SELECT a.Flt_Nbr as Flt_Nbr,
b.Airpt_Dstn as Airpt_Dstn,
a.Dpt_Airpt_Cd,
a.Arrv_Airpt_Cd
from a
LEFT join b on replace(replace(a.Flt_Rte_Cd,a.Dpt_Airpt_Cd+'-',''),'-'+a.Arrv_Airpt_Cd,'') = b.Dpt_Airpt_Cd
and a.Arrv_Airpt_Cd = b.Arrv_Airpt_Cd
where Flt_Rte_Cd like '%-%-%') c
inner join ( SELECT a.Flt_Nbr as Flt_Nbr,
b.Airpt_Dstn as Airpt_Dstn
from a
LEFT join b on replace(replace(a.Flt_Rte_Cd,a.Dpt_Airpt_Cd+'-',''),'-'+a.Arrv_Airpt_Cd,'') = b.Arrv_Airpt_Cd
and a.Dpt_Airpt_Cd = b.Dpt_Airpt_Cd
where Flt_Rte_Cd like '%-%-%') d on c.Flt_Nbr =d.Flt_Nbr
union all
select a.Dpt_Airpt_Cd,
a.Arrv_Airpt_Cd,
b.Airpt_Dstn as Airpt_Dstn
from a
inner join b on a.Dpt_Airpt_Cd = b.Dpt_Airpt_Cd and a.Arrv_Airpt_Cd = b.Arrv_Airpt_Cd
where a.Flt_Nbr not in (select Flt_Nbr from a where Flt_Rte_Cd like '%-%-%')
结果
Dpt_Airpt_Cd Arrv_Airpt_Cd Airpt_Dstn
HRB NKG 0
CKG HRB 870
CKG CTU 500
PEK CKG 600
create table hangban(Flt_Nbr int,Flt_Rte_Cd varchar(100),Dpt_Airpt_Cd varchar(10),Arrv_Airpt_Cd varchar(10))
insert into hangban values('8023','CKG-CTU','CKG','CTU')
insert into hangban values('8045','PEK-CKG','PEK','CKG')
insert into hangban values('8078','CKG-NKG-HRB','CKG','HRB')
insert into hangban values('8056','HRB-CKG-NKG','HRB','NKG')
go
create table juli(Dpt_Airpt_Cd varchar(10),Arrv_Airpt_Cd varchar(10),Airpt_Dstn int)
insert into juli values('CKG','CTU',500)
insert into juli values('PEK','CKG',600)
insert into juli values('CKG','NKG',510)
insert into juli values('NKG','HRB',360)
insert into juli values('HRB','CKG',130)
gocreate function C_juli(@Flt_Rte_Cd varchar(100))
returns int
as
begin
declare @juli int
declare @temp_juli int
declare @begin varchar(10)
declare @end varchar(10) set @juli = 0 while charindex('-',@Flt_Rte_Cd)>0
begin
select @begin = substring(@Flt_Rte_Cd,1,charindex('-',@Flt_Rte_Cd)-1)
select @Flt_Rte_Cd = substring(@Flt_Rte_Cd,charindex('-',@Flt_Rte_Cd)+1,8000) if charindex('-',@Flt_Rte_Cd)=0
select @end = @Flt_Rte_Cd
else
select @end = substring(@Flt_Rte_Cd,1,charindex('-',@Flt_Rte_Cd)-1) select @juli = @juli + Airpt_Dstn from juli
where (Dpt_Airpt_Cd = @begin and Arrv_Airpt_Cd = @end)
or(Dpt_Airpt_Cd = @end and Arrv_Airpt_Cd = @begin)
end
return @juli
end
goselect *,dbo.C_juli(Flt_Rte_Cd) from hangban--drop table hangban
--drop table juli
--drop function C_juli
create table hangban(Flt_Nbr int,Flt_Rte_Cd varchar(100),Dpt_Airpt_Cd varchar(10),Arrv_Airpt_Cd varchar(10))
insert into hangban values('8023','CKG-CTU','CKG','CTU')
insert into hangban values('8045','PEK-CKG','PEK','CKG')
insert into hangban values('8078','CKG-NKG-HRB','CKG','HRB')
insert into hangban values('8056','HRB-CKG-NKG','HRB','NKG')
create table juli(Dpt_Airpt_Cd varchar(10),Arrv_Airpt_Cd varchar(10),Airpt_Dstn int)
insert into juli values('CKG','CTU',500)
insert into juli values('PEK','CKG',600)
insert into juli values('CKG','NKG',510)
insert into juli values('NKG','HRB',360)
insert into juli values('HRB','CKG',130)
goselect a.*,b.airpt_dstn from hangban a,juli b
where a.Dpt_Airpt_Cd = b.Dpt_Airpt_Cd and a.Arrv_Airpt_Cd=b.Arrv_Airpt_Cd
union
select a.*,b.Airpt_Dstn+c.Airpt_Dstn from hangban a,juli b,juli c
where a.Dpt_Airpt_Cd = b.Dpt_Airpt_Cd and a.Arrv_Airpt_Cd=c.Arrv_Airpt_Cd
and b.Arrv_Airpt_Cd = c.Dpt_Airpt_Cd
go
drop table hangban,juli
/*
Flt_Nbr Flt_Rte_Cd Dpt_Airpt_Cd Arrv_Airpt_Cd airpt_dstn
----------- ---------------------------------------------------------------------------------------------------- ------------ ------------- -----------
8023 CKG-CTU CKG CTU 500
8045 PEK-CKG PEK CKG 600
8056 HRB-CKG-NKG HRB NKG 640
8078 CKG-NKG-HRB CKG HRB 870
*/
create table hangban(Flt_Nbr int,Flt_Rte_Cd varchar(100),Dpt_Airpt_Cd varchar(10),Arrv_Airpt_Cd varchar(10))
insert into hangban values('8023','CKG-CTU','CKG','CTU')
insert into hangban values('8045','PEK-CKG','PEK','CKG')
insert into hangban values('8078','CKG-NKG-HRB','CKG','HRB')
insert into hangban values('8056','HRB-CKG-NKG','HRB','NKG')
go
create table juli(Dpt_Airpt_Cd varchar(10),Arrv_Airpt_Cd varchar(10),Airpt_Dstn int)
insert into juli values('CKG','CTU',500)
insert into juli values('PEK','CKG',600)
insert into juli values('CKG','NKG',510)
insert into juli values('NKG','HRB',360)
insert into juli values('HRB','CKG',130)
go
select *,
距离=(select sum(Airpt_Dstn) from juli
where charindex('-'+Dpt_Airpt_Cd+'-'+Arrv_Airpt_Cd+'-','-'+a.Flt_Rte_Cd+'-')>0)
from hangban a
航班航线,航班号,也显示在列表中 如:
Flt_Nbr Flt_Rte_Cd Airpt_Dstn
8023 CKG-CTU 300
8045 PEK-CKG 400
8078 CKG-NKG-HRB 900
8056 HRB-CKG-NKG 700
8089 CKG_HRB 500
上面的这些我都在数据库中执行过了
--直接用这个语句不行么?
select *,
距离=(select sum(Airpt_Dstn) from juli
where charindex('-'+Dpt_Airpt_Cd+'-'+Arrv_Airpt_Cd+'-','-'+a.Flt_Rte_Cd+'-')>0)
from hangban a
select c.Flt_Nbr,
c.Flt_Rte_Cd,
ISNULL(c.Airpt_Dstn + d.Airpt_Dstn,0) as Airpt_Dstn
from ( SELECT a.Flt_Nbr as Flt_Nbr,
a.Flt_Rte_Cd as Flt_Rte_Cd,
b.Airpt_Dstn as Airpt_Dstn
from a
LEFT join b on replace(replace(a.Flt_Rte_Cd,a.Dpt_Airpt_Cd+'-',''),'-'+a.Arrv_Airpt_Cd,'') = b.Dpt_Airpt_Cd
and a.Arrv_Airpt_Cd = b.Arrv_Airpt_Cd
where Flt_Rte_Cd like '%-%-%') c
inner join ( SELECT a.Flt_Nbr as Flt_Nbr,
b.Airpt_Dstn as Airpt_Dstn
from a
LEFT join b on replace(replace(a.Flt_Rte_Cd,a.Dpt_Airpt_Cd+'-',''),'-'+a.Arrv_Airpt_Cd,'') = b.Arrv_Airpt_Cd
and a.Dpt_Airpt_Cd = b.Dpt_Airpt_Cd
where Flt_Rte_Cd like '%-%-%') d on c.Flt_Nbr =d.Flt_Nbr
union all
select a.Flt_Nbr,
a.Flt_Rte_Cd,
b.Airpt_Dstn as Airpt_Dstn
from a
inner join b on a.Dpt_Airpt_Cd = b.Dpt_Airpt_Cd and a.Arrv_Airpt_Cd = b.Arrv_Airpt_Cd
where a.Flt_Nbr not in (select Flt_Nbr from a where Flt_Rte_Cd like '%-%-%')
order by c.Flt_Nbr
INSERT #a SELECT '0001', '上海-张家界-长沙-广州', '上海', '广州'
UNION ALL SELECT '0002', '上海-广州', '上海', '广州'
UNION ALL SELECT '0003', '张家界-长沙-广州', '张家界', '广州'
UNION ALL SELECT '0004', '广州-张家界', '广州', '张家界'
UNION ALL SELECT '0002', '广州-张家界-上海', '广州', '上海'CREATE TABLE #b(Dpt_Airpt_Cd varchar(10), Arrv_Airpt_Cd varchar(10), Airpt_Dstn int)
INSERT #b SELECT '上海', '张家界', 100
UNION ALL SELECT '长沙','广州', 50
UNION ALL SELECT '上海','广州', 120
UNION ALL SELECT '张家界','长沙', 20
UNION ALL SELECT '张家界','广州', 50select *,Total_Dstn=isnull((
select sum(Airpt_Dstn) from #b
where
charindex('-'+Dpt_Airpt_Cd+'-'+Arrv_Airpt_Cd+'-','-'+a.Flt_Rte_Cd+'-')>0
or
charindex('-'+Arrv_Airpt_Cd+'-'+Dpt_Airpt_Cd+'-','-'+a.Flt_Rte_Cd+'-')>0),0)
from #a adrop table #a,#b/*
0001 上海-张家界-长沙-广州 上海 广州 170
0002 上海-广州 上海 广州 120
0003 张家界-长沙-广州 张家界 广州 70
0004 广州-张家界 广州 张家界 50
0005 广州-张家界-上海 广州 上海 150
*/
原来高人早有写出看来撞车了我这个貌似添加了个功能,如果你距离表里起始和航班起始互相颠倒,我也能判断距离,不知道楼主是否有用~不用解析字符串。直接分行计算sum,where限定条件即可
距离=(select sum(Airpt_Dstn) from juli
where charindex('-'+Dpt_Airpt_Cd+'-'+Arrv_Airpt_Cd+'-','-'+a.Flt_Rte_Cd+'-')>0)
from hangban a
对于这个
不知道为什么我今天再公司用这个总是有些不对,可能是有的距离数据那个表中没有
我刚才再宿舍试了下,就是那个如果在表a中存在的数据比如HRB-BHY-NKG 但是在表b中并没有HRB-BHY和BHY-NKG 的距离就毁显示null,如果在数据库只存在HRB-BHY的距离而没存在BHY-NKG的距离就会只计算HRB-BHY的距离。
明天我问问这种人为情况应该怎么处理