taba
航班名 机场 里程 票价
a70 bj 0 0
a70 sh 1000 100/150
a70 nw 2000 200/250
a70 ua 4000 300/400
a80 pr 0 0输入 sh ua输出
航班 里程 票价
a70 3000 200/250
计算方式
里程(4000-1000)=3000
票价(300-100)=200
400-150=350
请帮忙写这个语句,谢谢
航班名 机场 里程 票价
a70 bj 0 0
a70 sh 1000 100/150
a70 nw 2000 200/250
a70 ua 4000 300/400
a80 pr 0 0输入 sh ua输出
航班 里程 票价
a70 3000 200/250
计算方式
里程(4000-1000)=3000
票价(300-100)=200
400-150=350
请帮忙写这个语句,谢谢
use tempdb
go
create table taba
(
航班名 varchar(48) not null,
机场 varchar(48) not null,
里程 int not null,
票价 int not null
)
go
insert into taba values ('a70','bj',0,0)
,('a70','sh',1000,100),('a70','nw',2000,200),
('a70','ua',4000,300),('a80','pr',0,0)create procedure jipiao(@s varchar(48),@e varchar(48))
as
begin select 航班名,abs((select s.里程 from taba as s where 机场=@s)-(select e.里程 from taba as e where 机场=@e)) as 里程,
ABS((select s.票价 from taba as s where 机场=@s)-(select e.票价 from taba as e where 机场=@e)) as 票价 from taba
endexec jipiao 'sh','ua'
这些写法能求出来..但是不对..该去上班了..到公司在写
go
create table [taba] (航班名 nvarchar(6),机场 nvarchar(4),里程 int,票价 nvarchar(14))
insert into [taba]
select 'a70','bj',0,'0' union all
select 'a70','sh',1000,'100/150' union all
select 'a70','nw',2000,'200/250' union all
select 'a70','ua',4000,'300/400' union all
select 'a80','pr',0,'0'select * from [taba]
declare @from varchar(10)
declare @To varchar(10)set @from ='sh'
Set @to = 'ua';with TT
as(
select 航班名,
[里程] = sum(case 机场 when @to then 里程 else -里程 end),
[票价1]=sum(case 机场 when @to then convert(int,left(票价,patindex('%/%',票价)-1)) else -convert(int,left(票价,patindex('%/%',票价)-1)) end),
[票价2]=sum(case 机场 when @to then convert(int,right(票价,len(票价) - patindex('%/%',票价))) else -convert(int,right(票价,len(票价) - patindex('%/%',票价))) end)
from taba where 机场 in( @from,@to)
group by 航班名
)
select * from TT
/*
航班名 里程 票价1 票价2
a70 3000 200 250
*/参考!
a70 sh ua 3000 200 250 */另外有票价3 怎么办,也就是 300/400/500 这样的格式,能容错吗?或者 票价显示- 的不显示票价例子
a70 nw 2000 200/250/500
a70 ua 4000 -
a70 sh ua 3000 200 250 */另外有票价3 怎么办,也就是 300/400/500 这样的格式,能容错吗?或者 票价显示- 的不显示票价例子
a70 nw 2000 200/250/500
a70 ua 4000 -
a70 bj ua 3000 200 250 */
bj ua
create table t1
(
name varchar(10),
jichang varchar(10),
licheng int,
piaojia varchar(20)
)
insert into t1
select 'a70', 'bj', 0, '0' union all
select 'a70', 'sh', 1000, '100/150' union all
select 'a70', 'nw', 2000, '200/250' union all
select 'a70', 'ua', 4000, '300/400' union all
select 'a80', 'pr', 0, '0'
select * from t1declare @a varchar(10),
@b varchar(10)
select @a='sh',@b='ua'
;with aaa as
(select * from
(select name as name1,licheng as licheng1,piaojia as piaojia1 from t1 where jichang=@b) as a inner join
(select name as name2,licheng as licheng2,piaojia as piaojia2 from t1 where jichang=@a) as b on a.name1=b.name2)
select name1 as [航班],licheng1-licheng2 as [里程],
cast(cast(LEFT(piaojia1,CHARINDEX('/',piaojia1)-1) as int)-cast(LEFT(piaojia2,CHARINDEX('/',piaojia2)-1) as int) as varchar)
+'/'+
cast(cast(right(piaojia1,len(piaojia1)-CHARINDEX('/',piaojia1)) as int)-cast(right(piaojia2,len(piaojia2)-CHARINDEX('/',piaojia2)) as int) as varchar)
as [票价]
from aaa我建议你把票价分开,不要放在一个字段里。
a70 bj ua 3000 “300/400-200/500”含始发,和终到机场的
但目前比较急数据库结构不方便大动,而且再建表数据库有点冗余。那就这先这样,生成个字符串,由程序计算,你看行吗
能帮忙实现这样的吗,比较急 含始发和终到机场的,输入 sh,ua
显示的是bj始发的/* 航班名 始发机场 终到机场 里程 票价公式字符串
a70 bj ua 3000 “300/400-200/500”
a70 bj ua 3000 “300/400-200/500”
(
name varchar(10),
jichang varchar(10),
licheng int,
piaojia varchar(20)
)
insert into t1
select 'a70', 'bj', 0, '0' union all
select 'a70', 'sh', 1000, '100/150' union all
select 'a70', 'nw', 2000, '200/250' union all
select 'a70', 'ua', 4000, '300/400' union all
select 'a80', 'pr', 0, '0'
select * from t1
declare @a varchar(10),
@b varchar(10)
select @a='sh',@b='ua'select b.name,c.jichang as 始发机场,b.jichang as 终到机场,
b.licheng-a.licheng as 里程,b.piaojia+'-'+a.piaojia as 票价
from
(select * from t1 where jichang=@a) a,
(select * from t1 where jichang=@b) b,
(select * from t1 where licheng=0) c
where a.name=b.name and b.name=c.namename 始发机场 终到机场 里程 票价
---------- ---------- ---------- ----------- -----------------------------------------
a70 bj ua 3000 300/400-100/150(所影响的行数为 1 行)
DECLARE @j VARCHAR(10)SET @i='sh'
SET @j = 'ua'select 航班名,
[始发机场]=@i,
[终点机场]=@j,
里程 = SUM(CASE WHEN 机场 = @i THEN -里程 ELSE 里程 end), 票价=stuff((select '-'+票价 from taba t where 航班名=taba.航班名 AND 机场 IN(@i,@j) ORDER BY 票价 desc for xml path('')), 1, 1, '')
from dbo.taba
WHERE 机场 IN('sh','ua')
group by 航班名
/*
航班名 始发机场 终点机场 里程 票价
a70 sh ua 3000 300/400-100/150*/
a70 bj 0 0
a70 sh 1000 100/150
a70 nw 2000 200/250
a70 ua 4000 300/400
a70 es 5000 400/600
a80 pr 0 0应该为
name 始发机场 终到机场 里程 票价
---------- ---------- ---------- ----------- -----------------------------------------
a70 bj es 3000 300/400-100/150
下面的这个能改改吗
create table t1
(
name varchar(10),
jichang varchar(10),
licheng int,
piaojia varchar(20)
)
insert into t1
select 'a70', 'bj', 0, '0' union all
select 'a70', 'sh', 1000, '100/150' union all
select 'a70', 'nw', 2000, '200/250' union all
select 'a70', 'ua', 4000, '300/400' union all
select 'a70', 'es', 5000, '400/500' union all
select 'a80', 'pr', 0, '0'
select * from t1
declare @a varchar(10),
@b varchar(10)
select @a='sh',@b='ua'select b.name,c.jichang as 始发机场,b.jichang as 终到机场,
b.licheng-a.licheng as 里程,b.piaojia+'-'+a.piaojia as 票价
from
(select * from t1 where jichang=@a) a,
(select * from t1 where jichang=@b) b,
(select * from t1 where licheng=0) c
where a.name=b.name and b.name=c.name另外,会出现 在将 varchar 值 '广州' 转换成数据类型 int 时失败。
create table t1
(
name varchar(10),
jichang varchar(10),
licheng int,
piaojia varchar(20)
)
insert into t1
select 'a70', 'bj', 0, '0' union all
select 'a70', 'sh', 1000, '100/150' union all
select 'a70', 'nw', 2000, '200/250' union all
select 'a70', 'ua', 4000, '300/400' union all
select 'a70', 'es', 5000, '400/500' union all
select 'a80', 'pr', 0, '0'
select * from t1
declare @a varchar(10),
@b varchar(10)
select @a='sh',@b='ua'select b.name,c.jichang as 始发机场,d.jichang as 终到机场,
b.licheng-a.licheng as 里程,b.piaojia+'-'+a.piaojia as 票价
from
(select * from t1 where jichang=@a) a,
(select * from t1 where jichang=@b) b,
(select * from t1 where licheng=0) c,
t1 d
where a.name=b.name and b.name=c.name and c.name=d.name
and not exists (select 1 from t1 where t1.name=d.name and t1.licheng>d.licheng)
/*
name 始发机场 终到机场 里程 票价
---------- ---------- ---------- ----------- -----------------------------------------
a70 bj es 3000 300/400-100/150(所影响的行数为 1 行)
*/--下面这个'广州'在你给出的数据记录中没有的,所有看不出
--另外,会出现 在将 varchar 值 '广州' 转换成数据类型 int 时失败。
select * into #tb from tabaselect #ta.航班名,#ta.机场 as 起始机场,#tb.机场 as 终止机场,abs(#ta.里程 - #tb.里程) as 里程,abs(#ta.票价 - #tb.票价) as 票价 from #ta,#tb
where #ta.机场 ='sh' and #tb.机场 ='ua'
create table #taba
(航班名 nvarchar(10),机场 nvarchar(10),里程 int,票价 nvarchar(10))
insert #taba
select 'a70','bj',0,'0' union all
select 'a70','sh',1000,'100/150' union all
select 'a70','nw',2000,'200/250' union all
select 'a70','ua',4000,'300/400' union all
select 'a80','pr',0,'0' declare @i nvarchar(10)
declare @j nvarchar(10)
set @i='sh'
set @j='ua'
select t1.航班名,t1.里程-t2.里程 as 里程,
case when charindex('/',t1.票价)>0 then
(
case when charindex('/',t2.票价)>0 then
(
Ltrim(cast(left(t1.票价,charindex('/',t1.票价)-1) as int)-
cast(left(t2.票价,charindex('/',t2.票价)-1) as int))+'/'+
Ltrim(cast(right(t1.票价,len(t1.票价)-charindex('/',t1.票价)) as int)-
cast(right(t2.票价,len(t2.票价)-charindex('/',t2.票价)) as int))
)
else t1.票价 end
)
else '0' end as 票价
from #taba as t1 join #taba as t2 on t1.机场=@j and t2.机场=@i