select ROW_NUMBER() over(order by zl) as row_num,* from(
select GetDistance(Lng,Lat,116.3272857,39.9407463) as zl,* from SHOP where pkid between 1 and 10
)t提示出错:Arithmetic overflow error converting expression to data type float.
问题非常奇怪.
1,把over(order by zl)改为over(order by pkid)不抛错了,但是排序会有问题
2,把第2个select的*改为字段名又对了,如果写出全部字段又会抛错,任意去掉一个又不抛了
3,去掉ROW_NUMBER() 不会抛错
4,绝对不是GetDistance函数里面报的错,因为执行第2行代码的时候可以得到结果。如果我用临时表以上问题都解决了,急需知道是什么原因,请大家帮帮忙,感激不尽!
下面是临时表:
select *,[Travel].[dbo].GetDistance(Lng,Lat,116.327285766602,39.940746307373) as zl into #temp3 from [Travel].[dbo].[FoodShop]
where pkid between 1 and 100
select ROW_NUMBER() over(Order by zl) as row_num,* from #temp3
Drop table #temp3
select GetDistance(Lng,Lat,116.3272857,39.9407463) as zl,* from SHOP where pkid between 1 and 10
)t提示出错:Arithmetic overflow error converting expression to data type float.
问题非常奇怪.
1,把over(order by zl)改为over(order by pkid)不抛错了,但是排序会有问题
2,把第2个select的*改为字段名又对了,如果写出全部字段又会抛错,任意去掉一个又不抛了
3,去掉ROW_NUMBER() 不会抛错
4,绝对不是GetDistance函数里面报的错,因为执行第2行代码的时候可以得到结果。如果我用临时表以上问题都解决了,急需知道是什么原因,请大家帮帮忙,感激不尽!
下面是临时表:
select *,[Travel].[dbo].GetDistance(Lng,Lat,116.327285766602,39.940746307373) as zl into #temp3 from [Travel].[dbo].[FoodShop]
where pkid between 1 and 100
select ROW_NUMBER() over(Order by zl) as row_num,* from #temp3
Drop table #temp3
(select GetDistance(Lng,Lat,116.3272857,39.9407463) as zl,* from SHOP where pkid between 1 and 10)
select ROW_NUMBER() over(order by zl) as row_num,* from t
--或许是:
--括号里面子查询的星号(*)要用具体的字段名代替!
--因为你前面已经用到了函数字段--要不这样试试:
select ROW_NUMBER() over(order by zl) as row_num,* from(
select GetDistance(Lng,Lat,116.3272857,39.9407463) as zl,s.* from SHOP s where pkid between 1 and 10
)t
select ROW_NUMBER() over(order by convert(decimal(36,8),zl)) as row_num,* from(
select GetDistance(Lng,Lat,116.3272857,39.9407463) as zl,* from SHOP where pkid between 1 and 10
)t
尝试这个,
如果把
GetDistance(Lng,Lat,116.327285766602,39.940746307373)的结果转换为int,float,varchar等。也不抛错了。实在搞不明白什么问题
大家可能会说是数据问题,我看了大半天也没看出数据有问题
select
(SELECT COUNT(*) from(
select GetDistance(Lng,Lat,116.3272857,39.9407463) as zl,* from SHOP where pkid between 1 and 10
)t WHERE T.ZL<=A.ZL)AS ROW_NUMM,
*
FROM
(
select GetDistance(Lng,Lat,116.3272857,39.9407463) as zl,* from SHOP where pkid between 1 and 10
)A
;WITH T AS(
select GetDistance(Lng,Lat,116.3272857,39.9407463) as zl,* from SHOP where pkid between 1 and 10
)
select ROW_NUMBER() over(order by zl) as row_num,* from T