日期:2014-05-18  浏览次数:20747 次

关于SQl中碰到的一个难点 高手进
比如说一张坐标表中
id smx smy
1 1.1 1.5
2 2.2 2.6
3 3.3 3.7
4 4.4 6.0
5 5.5 6.1

2个点产生一条线

第一行是一个点 第二行是一个点

两个点是一条线  
然后我最后要计算 第一行到第五行 形成的线段的长度
我用SQL语句应该怎么实现

坐标求线段公式为:d=V[(x1-x2)^2+(y1-y2)^2] 

高分求助!

------解决方案--------------------
SQL code

--生成测试数据
;with T as (
select 1 as ID ,1.1 as smx,1.5 as smy union all 
select 2 as ID ,2.2 as smx,2.6 as smy union all
select 3 as ID ,3.3 as smx,3.7 as smy union all
select 4 as ID ,4.4 as smx,6.0 as smy union all
select 5 as ID ,5.5 as smx,6.1 as smy 
),T2 as 
(
    select a.*,b.smx as smx2,b.smy as smy2 from t a inner join t b on a.id = b.id -1

)
--求线段距离
--select * , SQRT(SQUARE(SMX2-SMX) + SQUARE(SMY2-SMY)) as [线段距离]
--from T2
--求总距离
select sum(SQRT(SQUARE(SMX2-SMX) + SQUARE(SMY2-SMY)) )as [总距离]
from T2