mysql 累计值做递减

使用mysql 临时变量

具体方法

SELECT date,value, @tempvar:=@tempvar+value
FROM test_tb,(SELECT @tempvar:=0)T
ORDER BY date DESC

实现业务 对dev 进行分组递减每月 年的方法和这个类似 应为数据都是累计值 并没有拆分出来 只好用每月的最后一条数据做递减 递增也是和这个意思差不多，大佬有好的办法 可以留言 嘻嘻

SELECT
    //根据自己业务进行递减 因为我要分组并找出每月的最后一条所以只能嵌套       查询了
	ROUND( @tempvar := @tempvar - a.add_kwh, 2 ) AS kwh,
	ROUND( @tempvar := @tempvar / 261.4, 2 ) AS area,
	a.add_kwh,
	a.ts 
FROM
	(
	SELECT
	    //统计每个月的分组
		sum( c.add_Kwh ) AS add_Kwh,
		d.ts 
	FROM
	//得到分组的电量
		dev_photovoltaic c,(
		SELECT
			Max( id ) AS id,
			MONTH ( ts ) AS ts,
			add_Kwh 
		FROM
			dev_photovoltaic 
		GROUP BY
			MONTH ( ts ),
			dev 
			) d,(
			//重点 这里你可以赋值为0 应为我需要用上一个值减去下一个值要动 态的所以查询出数据的上一条
		SELECT
			@tempvar :=(
			SELECT
				IFNULL(	sum( ct.add_Kwh ) ,0)
			FROM
				dev_photovoltaic ct,
				(
				SELECT
					MAX( id ) AS id,
					MONTH ( ts ) AS ts 
				FROM
					dev_photovoltaic 
				WHERE
					PERIOD_DIFF( date_format( now(), '%Y%m' ),             date_format( ts, '%Y%m' ) ) = 1 
				GROUP BY
					MONTH ( ts ),
					dev 
				) AS s 
			WHERE
				s.id = ct.id 
			GROUP BY
				s.ts 
			) 
		) T 
	WHERE
		c.id = d.id 
	GROUP BY
		d.ts 
	ORDER BY
		d.ts ASC 
	) a