sql server某列数据值逐行累加

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

  查询结果:

sql server某列数据值逐行累加

 

sql server某列数据值逐行累加

上一篇:Databus 分布式数据库同步系统


下一篇:Photoshop为树林美女图片调制出秋季唯美阳光色