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