在将 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 始发机场,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 /* name 始发机场 终到机场 里程 票价 ---------- ---------- ---------- ----------- ----------------------------------------- a70 bj ua 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 始发机场,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 时失败。
这个广州在哪,我怎么没有看到。
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
/*
name 始发机场 终到机场 里程 票价
---------- ---------- ---------- ----------- -----------------------------------------
a70 bj ua 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 始发机场,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 时失败。