mysql 查询生成对账单 当前数据为上条数据累计
需求
思路:期初数据做个对账开始时间之前的数据,查询时以时间为排序条件查询
- 分别做出 收款 核销 期初 等基础数据
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
- 增加余额字段
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
- 解决分页问题
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
查询条件及其它字段自定义
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤