发帖
雷达卡
通过SQL与存储过程实现畅捷通T+数据清理
由于特殊业务需求，客户需对畅捷通T+系统中的历史数据进行全面清理。为提升处理效率，采用SQL语句结合存储过程的方式，快速完成指定范围的数据清除操作。
数据清理范围说明
本次清理涵盖以下核心模块：
- 财务模块：包括总账凭证、余额表、会计期间期初数据及固定资产卡片信息。
- 供应链模块：涉及采购、销售、调拨、库存、生产加工等各类单据。
- 零售模块：包含零售交班、对账收款、结算单据以及门店零售交易记录。
对于零售单据，要求按门店和日期进行分组，从每组的最新时间点开始向前删除，仅保留每日交易总额中约45%的单据数据。
会员数据需按会员类别进行分类处理，每个类别保留约45%的记录，并剔除部分特定标识的特殊会员。
数据清理执行方案
为确保清理效率并避免事务日志过度增长，对可直接清空的总账与供应链单据采用TRUNCATE方式处理；会员数据则通过条件筛选实现比例性删除。
核心清理脚本（请在生产环境谨慎执行）
-- 清除总账凭证及相关记录 TRUNCATE TABLE GL_Entry; TRUNCATE TABLE GL_Doc; -- 清理总账余额与期初数据 TRUNCATE TABLE GL_Balance; TRUNCATE TABLE GL_AccountPeriodBegin; -- 删除固定资产卡片及其附件 TRUNCATE TABLE AM_Attachment; TRUNCATE TABLE AM_Asset; -- 清理采购相关单据 TRUNCATE TABLE PU_PurchaseArrival; TRUNCATE TABLE PU_PurchaseArrival_b; TRUNCATE TABLE PU_PurchaseArrival_SourceRelation; -- 删除销售预测单 TRUNCATE TABLE MP_IntendSaleOrder_b; TRUNCATE TABLE MP_IntendSaleOrder; -- 清除销售订单及其明细 TRUNCATE TABLE SA_SaleOrder_SourceRelation; TRUNCATE TABLE SA_SaleOrder_b; TRUNCATE TABLE SA_SaleOrder; -- 删除销货单据 TRUNCATE TABLE SA_SaleDeliveryPreReceive; TRUNCATE TABLE SA_SaleDelivery_b; TRUNCATE TABLE SA_SaleDelivery; -- 清理要货申请单 TRUNCATE TABLE DI_StockRequest_b; TRUNCATE TABLE DI_StockRequest; -- 删除调拨单 TRUNCATE TABLE ST_TransSourceRelation; TRUNCATE TABLE ST_TransVoucher_b; TRUNCATE TABLE ST_TransVoucher; -- 清除形态转换单 TRUNCATE TABLE ST_ShapeVoucher_b; TRUNCATE TABLE ST_ShapeVoucher; -- 清理盘点相关数据 TRUNCATE TABLE ST_CheckVoucher_b; TRUNCATE TABLE ST_CheckVoucher; TRUNCATE TABLE ST_AssistantDataBook; TRUNCATE TABLE ST_PartialCheckVoucher; TRUNCATE TABLE ST_PartialCheckVoucherDetail; -- 删除生产加工单信息 TRUNCATE TABLE MP_ManufactureOrder_Trace; TRUNCATE TABLE MP_ManufactureOrder_SourceRelation; TRUNCATE TABLE MP_ManufactureOrder_Material; TRUNCATE TABLE MP_ManufactureOrder_b; TRUNCATE TABLE MP_ManufactureOrder; -- 清除零售结算单 TRUNCATE TABLE RE_RetailSettle_b; TRUNCATE TABLE RE_RetailSettle; -- 删除零售交班单 TRUNCATE TABLE RE_ShiftVoucher_b; TRUNCATE TABLE RE_ShiftVoucher; -- 清理零售对账收款明细 TRUNCATE TABLE RE_ReceiveDetail; -- 删除全部出入库单据及关联关系 TRUNCATE TABLE RE_SaleOutRelation; -- 零售单与出入库关系表 TRUNCATE TABLE ST_RDRecordSourceRelation; TRUNCATE TABLE ST_RDRecord_b; TRUNCATE TABLE ST_RDRecord;
数据保留逻辑实现
在完成大规模清理后，需导入需保留的部分数据。针对会员信息，采用如下分组保留策略：
-- 按会员卡号与会员类型分组，组内随机排序以实现均匀抽样 SELECT cardcode, -- 会员卡号（对应test1表中的cnumber） idmembertype, -- 会员类型ID -- 后续可通过ROW_NUMBER()或NTILE()函数实现每类保留45%的逻辑
该查询将作为后续保留逻辑的基础，结合窗口函数实现精准的比例控制。
重要提示：以上操作涉及大量数据删除，务必在测试环境充分验证后，于生产环境由专业人员执行，并提前做好完整数据备份。
-- 查询账套信息
select * from EAP_Account;
-- 创建临时表，按会员类型分组并随机排序，同时计算每组总记录数
SELECT
cardcode,
idmembertype,
ROW_NUMBER() OVER (PARTITION BY idmembertype ORDER BY NEWID()) AS Random_Row,
COUNT(*) OVER (PARTITION BY idmembertype) AS Total_Rows
INTO tmp_xxx
FROM AA_DR_MemberNew
WHERE cardcode NOT IN (SELECT cnumber FROM test1 WHERE cnumber IS NOT NULL);
-- 将每组前45%的会员卡号插入test1表（标记为自动保留）
INSERT INTO test1 (cnumber)
SELECT DISTINCT cardcode
FROM tmp_xxx
WHERE Random_Row <= CEILING(Total_Rows * 0.45);
-- 删除AA_DR_MemberNew中未被保留在test1中的会员数据
DELETE FROM AA_DR_MemberNew
WHERE cardcode NOT IN (SELECT DISTINCT cnumber FROM test1 WHERE cnumber IS NOT NULL);
-- 汇总指定门店和时间段内的零售单数据，写入新表
SELECT
idstore,
CONVERT(DATE, voucherdate) AS DealDate,
SUM(totaltaxamount) AS TotalRetailAmount,
0 AS IsProcessed
INTO Retail_Store_Date_Summary
FROM RE_Retail
WHERE idstore NOT IN (3,20,22,26)
AND voucherdate >= '2025-01-01 00:00:00.000'
AND voucherdate < '2026-01-01 00:00:00.000'
GROUP BY idstore, CONVERT(DATE, voucherdate);
-- 对比删除前后数据差异：查询2024年12月实际收入与目标汇总金额不一致的记录
select
CAST(t2.mdid AS VARCHAR),
CONVERT(VARCHAR(10), t1.DealDate, 23),
t2.ysje,
t1.TotalRetailAmount,
t2.ysje / t1.TotalRetailAmount
from RETAIL_STORE_DATE_SUMMARY as t1
INNER JOIN (
SELECT
idstore mdid,
CONVERT(DATE, voucherdate) as ysrq,
sum(totaltaxamount) as ysje
FROM RE_Retail
WHERE idstore NOT IN (3,20,22,26)
and voucherdate >= '2024-12-01 00:00:00.000'
and voucherdate < '2024-12-31 00:00:00.000'
group by idstore, CONVERT(DATE, voucherdate)
) as t2
on t1.idstore = t2.mdid
and t1.DealDate = t2.ysrq
where t1.TOTALRETAILAMOUNT > 0
and t2.ysje / t1.TotalRetailAmount != 1;
-- 零售单数据按比例保留的存储过程定义
IF OBJECT_ID('dbo.SP_DELETE_1', 'P') IS NOT NULL
DROP PROCEDURE dbo.SP_DELETE_1
GO
CREATE PROCEDURE [dbo].[SP_DELETE_1]
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@IDSTORE VARCHAR(50),
@DEALDATE NVARCHAR(50),
@TOTALRETAILAMOUNT FLOAT
-- 定义游标，遍历需处理的数据行
DECLARE TODO_DELELTE_LIST CURSOR FOR
SELECT
IDSTORE,
DEALDATE,
TOTALRETAILAMOUNT
FROM
RETAIL_STORE_DATE_SUMMARY
WHERE
TOTALRETAILAMOUNT > 0
and DealDate >= '2025-01-01'
END
DECLARE TODO_DELELTE_LIST CURSOR FOR
SELECT
IDSTORE,
DEALDATE,
TOTALRETAILAMOUNT
FROM
SummaryData
WHERE
DealDate < '2025-12-31'
AND IsProcessed = 0
ORDER BY
DEALDATE DESC,
IDSTORE ASC;
OPEN TODO_DELELTE_LIST;
FETCH NEXT FROM TODO_DELELTE_LIST INTO @IDSTORE, @DEALDATE, @TOTALRETAILAMOUNT;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '门店' + CAST(@IDSTORE AS VARCHAR) + '准备删除' + @DEALDATE + '零售单' + CAST(@TOTALRETAILAMOUNT AS VARCHAR);
DECLARE todo_pos_list CURSOR FOR
SELECT id, totaltaxamount
FROM RE_Retail
WHERE idstore = @IDSTORE
AND CONVERT(DATE, voucherdate) = @DEALDATE
AND totaltaxamount <> 0
ORDER BY id DESC;
OPEN todo_pos_list;
DECLARE @reid INT, @dqtotal FLOAT;
DECLARE @deletedAmount FLOAT = 0;
DECLARE @deletetotalredline FLOAT = @TOTALRETAILAMOUNT * 0.55;
FETCH NEXT FROM todo_pos_list INTO @reid, @dqtotal;
WHILE @@FETCH_STATUS = 0 AND @deletedAmount < @deletetotalredline
BEGIN
BEGIN TRANSACTION;
DELETE FROM re_retail_b WHERE idRetailDTO = @reid;
DELETE FROM re_retail WHERE id = @reid;
PRINT '删除' + CAST(@reid AS VARCHAR) + '金额' + CAST(@dqtotal AS VARCHAR) + '已删除金额' + CAST(@deletedAmount AS VARCHAR);
SET @deletedAmount = @deletedAmount + @dqtotal;
COMMIT TRANSACTION;
FETCH NEXT FROM todo_pos_list INTO @reid, @dqtotal;
END;
CLOSE todo_pos_list;
DEALLOCATE todo_pos_list;
FETCH NEXT FROM TODO_DELELTE_LIST INTO @IDSTORE, @DEALDATE, @TOTALRETAILAMOUNT;
END;
CLOSE TODO_DELELTE_LIST;
DEALLOCATE TODO_DELELTE_LIST;
对于部分需要排除的会员数据，可通过Excel导入方式进行处理，使用SSMS完成操作。
操作步骤如下：
- 在目标数据库上右键点击“数据”，选择“任务” → “导入数据”
- 在数据源选项中选择“Excel”文件类型
- 连接到SQL Server，并选择要导入的目标数据库
- 设置导入后的表名，确认无误后开始执行导入
京公网安备 11010802022788号
