sql语句示例:
DECLARE @pts varchar(max)=‘[{"x":5.801718000000000e+002,"y":3.633722000000000e+002,"z":-3.739560000000000e+002,"seq":9},{"x":5.822890000000000e+002,"y":3.669996000000000e+002,"z":-3.739560000000000e+002,"seq":8},{"x":6.849424000000000e+002,"y":5.428771000000000e+002,"z":-3.722434000000000e+002,"seq":7},{"x":6.879668000000000e+002,"y":5.480589000000000e+002,"z":-3.721940000000000e+002,"seq":6},{"x":6.910163000000000e+002,"y":5.532253000000000e+002,"z":-3.721003000000000e+002,"seq":5},{"x":7.614744000000000e+002,"y":6.725928000000000e+002,"z":-3.699347000000000e+002,"seq":4},{"x":7.645240000000000e+002,"y":6.777592000000000e+002,"z":-3.698410000000000e+002,"seq":3},{"x":7.675464000000000e+002,"y":6.829361000000000e+002,"z":-3.695856000000000e+002,"seq":2},{"x":8.171899000000000e+002,"y":7.679665000000000e+002,"z":-3.651350000000000e+002,"seq":1},{"x":8.202150000000000e+002,"y":7.731480000000000e+002,"z":-3.651350000000000e+002,"seq":0}]‘;--结束点项 with t1 as ( select convert(float,x) x,convert(float,y) y,convert(float,z) z,convert(int,seq) seq from (select JSON_VALUE(value,‘$.x‘) as x,JSON_VALUE(value,‘$.y‘) as y,JSON_VALUE(value,‘$.z‘) as z,JSON_VALUE(value,‘$.seq‘) as seq from openjson(@pts)) as m ), t2 as ( select distance,seq,x1,y1,z1,x2,y2,z2 from ( select a.seq,a.x x1,a.y y1,a.z z1,b.x x2,b.y y2,b.z z2, SQRT(SQUARE(b.x-a.x)+SQUARE(b.y-a.y)+SQUARE(b.z-a.z)) distance--返回前后两点的距离 from t1 a left join t1 b on a.seq=b.seq-1 ) A where distance is not null--排除最后一个点 ), t3 as ( select a.distance,a.seq,a.x1,a.y1,a.z1,a.x2,a.y2,a.z2,SUM(b.distance) as accumulation from t2 a inner join t2 b ON 1 = 1 where A.seq <= B.seq group by a.distance,a.seq,a.x1,a.y1,a.z1,a.x2,a.y2,a.z2 --order by a.seq desc ) select * from t3 order by seq desc
查询结果: