-- =============================================
-- 自动化删除订单校准汇总金额（修复过度删除问题）
-- =============================================
SET NOCOUNT ON; -- 关闭计数消息，简化输出
-- 1. 配置参数（按需修改）
DECLARE
@targetAmount DECIMAL(18,2) = 50000.00, -- 目标汇总金额
@date1 VARCHAR(20) = '2025-01-01 00:00:01', -- 时间范围开始
@date2 VARCHAR(20) = '2025-02-01 00:00:01', -- 时间范围结束
@maxDeleteAttempts INT = 1000, -- 最大删除尝试次数（防止死循环）
@currentAmount DECIMAL(18,2), -- 当前汇总金额
@amountDiff DECIMAL(18,2), -- 金额差值
@randomOrderId BIGINT, -- 随机订单ID
@deleteAttempts INT = 0, -- 已尝试删除次数
@deletedCount INT = 0, -- 实际删除成功数
@deletedOrderPrice DECIMAL(18,2); -- 待删除订单的金额
-- 2. 创建临时表存储符合条件的订单ID（含订单金额，便于计算）
IF OBJECT_ID('tempdb..#ValidOrders') IS NOT NULL
DROP TABLE #ValidOrders;
CREATE TABLE #ValidOrders (
OrderId BIGINT PRIMARY KEY,
ORPrice DECIMAL(18,2)
);
-- 插入符合条件的订单（已结账、指定时间范围）+ 订单金额
-- 【关键修正1】：过滤掉金额为0的订单
INSERT INTO #ValidOrders (OrderId, ORPrice)
SELECT F_ID, F_ORPrice
FROM T_CT_Order
WHERE F_Status = '已结账'
AND F_EndTime >= @date1
AND F_EndTime <= @date2
AND F_ORPrice > 0;
-- 3. 初始查询当前汇总金额
SELECT @currentAmount = SUM(ORPrice) FROM #ValidOrders;
PRINT '初始汇总金额: ' + CAST(@currentAmount AS VARCHAR(50)) + '，目标金额: ' + CAST(@targetAmount AS VARCHAR(50));
-- 4. 循环删除校准金额
WHILE 1=1
BEGIN
-- 计算金额差值（取绝对值）
SET @amountDiff = ABS(@currentAmount - @targetAmount);
-- 终止条件1：误差≤1000
IF @amountDiff <= 1000
BEGIN
PRINT '✅ 金额校准完成！当前金额: ' + CAST(@currentAmount AS VARCHAR(50))
+ '，目标金额: ' + CAST(@targetAmount AS VARCHAR(50))
+ '，误差: ' + CAST(@amountDiff AS VARCHAR(50));
BREAK;
END
-- 终止条件2：达到最大尝试次数
IF @deleteAttempts >= @maxDeleteAttempts
BEGIN
PRINT '⚠️ 已达到最大删除尝试次数（' + CAST(@maxDeleteAttempts AS VARCHAR(10)) + '），提前终止';
BREAK;
END
-- 终止条件3：无可用订单可删
IF NOT EXISTS (SELECT 1 FROM #ValidOrders)
BEGIN
PRINT '⚠️ 已无符合条件的订单可删除，当前金额: ' + CAST(@currentAmount AS VARCHAR(50));
BREAK;
END
-- 【关键修正2】：智能选择要删除的订单，避免过度删除
-- 随机获取1条待删除订单，并提前获取其金额
SELECT TOP 1
@randomOrderId = OrderId,
@deletedOrderPrice = ORPrice
FROM #ValidOrders
ORDER BY NEWID();
-- 判断：如果删除这个订单后，金额会低于目标金额，就跳过它，选择下一个
IF @currentAmount - @deletedOrderPrice < @targetAmount
BEGIN
PRINT '⏭️ 跳过订单ID: ' + CAST(@randomOrderId AS VARCHAR(20)) + '，金额: ' + CAST(@deletedOrderPrice AS VARCHAR(20)) + '（删除后金额将低于目标值）';
SET @deleteAttempts = @deleteAttempts + 1;
-- 从临时表移除这个订单，避免再次选中它
DELETE FROM #ValidOrders WHERE OrderId = @randomOrderId;
CONTINUE; -- 直接进入下一次循环
END
BEGIN TRY
BEGIN TRANSACTION;
-- 删除关联表数据
DELETE FROM T_CT_PayInfo WHERE F_OrderId = @randomOrderId;
DELETE FROM T_CT_PriceList WHERE F_OrderId = @randomOrderId;
-- 删除主订单表数据
DELETE FROM T_CT_Order WHERE F_ID = @randomOrderId;
COMMIT TRANSACTION;
-- 更新临时表和金额
DELETE FROM #ValidOrders WHERE OrderId = @randomOrderId;
SET @currentAmount = @currentAmount - @deletedOrderPrice;
SET @deletedCount = @deletedCount + 1;
SET @deleteAttempts = @deleteAttempts + 1;
-- 输出删除日志
PRINT '
