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 bj 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
a80 pr 0 0输入 sh ua怎样输出
航班 始发机场 终到机场 里程 票价
a70 bj ua 3000 300/400-100/150
(
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,a.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
where a.name=b.namename 始发机场 终到机场 里程 票价
---------- ---------- ---------- ----------- -----------------------------------------
a70 sh ua 3000 300/400-100/150(所影响的行数为 1 行)
(
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*/
???
name 始发机场 终到机场 里程 票价
---------- ---------- ---------- ----------- -----------------------------------------
a70 bj ua 3000 300/400-100/150