mysql 查询生成对账单 当前数据为上条数据累计

需求

思路：期初数据做个对账开始时间之前的数据，查询时以时间为排序条件查询

  1. 分别做出 收款 核销 期初 等基础数据
SELECT
	billdate,
CASE
	
	WHEN tradetypecode = 'D2' THEN
	'收款单' 
	WHEN tradetypecode = 'F2-Cxx-001' THEN
	'余额预收单' 
	WHEN tradetypecode = 'A0' THEN
	'期初' 
	END tradetypecode,
	billno,
	businame,
	remark,
	customercode,
CASE
		
		WHEN tradetypecode = 'D2' THEN
		money ELSE 0 
	END gather,
CASE
		
		WHEN tradetypecode = 'F2-Cxx-001' THEN
		money ELSE 0 
	END verification,
CASE
		
		WHEN tradetypecode = 'A0' THEN
		money ELSE 0 
	END beginning 
FROM
	teamwork_verify_gather 
WHERE
	customercode = 'P0680102' 
ORDER BY
	billdate ASC,
	tradetypecode DESC

  1. 增加余额字段
SELECT
	*,
	@total := ( beginning + @total + gather - verification ) AS 'balance' 
FROM
	(
SELECT
	billdate,
CASE
	
	WHEN tradetypecode = 'D2' THEN
	'收款单' 
	WHEN tradetypecode = 'F2-Cxx-001' THEN
	'余额预收单' 
	WHEN tradetypecode = 'A0' THEN
	'期初' 
	END tradetypecode,
	billno,
	businame,
	remark,
	customercode,
CASE
		
		WHEN tradetypecode = 'D2' THEN
		money ELSE 0 
	END gather,
CASE
		
		WHEN tradetypecode = 'F2-Cxx-001' THEN
		money ELSE 0 
	END verification,
CASE
		
		WHEN tradetypecode = 'A0' THEN
		money ELSE 0 
	END beginning 
FROM
	teamwork_verify_gather 
WHERE
	customercode = 'P0680102' 
ORDER BY
	billdate ASC,
	tradetypecode DESC 
	) AS bb,
	( SELECT @total := 0 ) AS T1

  1. 解决分页问题
SELECT
	* 
FROM
	(
SELECT
	*,
	@total := ( beginning + @total + gather - verification ) AS 'balance' 
FROM
	(
SELECT
	billdate,
CASE
	
	WHEN tradetypecode = 'D2' THEN
	'收款单' 
	WHEN tradetypecode = 'F2-Cxx-001' THEN
	'余额预收单' 
	WHEN tradetypecode = 'A0' THEN
	'期初' 
	END tradetypecode,
	billno,
	businame,
	remark,
	customercode,
CASE
		
		WHEN tradetypecode = 'D2' THEN
		money ELSE 0 
	END gather,
CASE
		
		WHEN tradetypecode = 'F2-Cxx-001' THEN
		money ELSE 0 
	END verification,
CASE
		
		WHEN tradetypecode = 'A0' THEN
		money ELSE 0 
	END beginning 
FROM
	teamwork_verify_gather 
WHERE
	customercode = 'P0680102' 
ORDER BY
	billdate ASC,
	tradetypecode DESC 
	) AS bb,
	( SELECT @total := 0 ) AS T1 
	) AS cc 
LIMIT 1,1

查询条件及其它字段自定义

posted on 2022-10-09 18:29  何苦->  阅读(220)  评论(2)    收藏  举报

导航