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   何苦->  阅读(193)  评论(2编辑  收藏  举报

相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示