时间戳

首页 > 解决方案 > 使用查询归档客户数据

sql - 使用查询归档客户数据

问题描述

我有一张Customers桌子，一张Transactions桌子，还有一张Payments桌子。该Transactions表代表对客户的收费，并且该Payments表代表对客户的信用。（两个表都有表的外键Customers。）

客户的余额是用Customers.StartingBalance加上表中该客户的所有费用Transactions的总和减去该客户在Payments表中的所有付款的总和来计算的。

现在我想实现一个存档功能，删除给定日期之前的所有交易和付款，然后更新Customers.StartingBalance以使最终余额（如上一段所述计算）保持不变。

这是我到目前为止所拥有的：

ALTER PROCEDURE [dbo].[ArchiveData] @ArchiveDateTime DATETIME
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @CustomerBalance TABLE
    (
        CustomerId INT,
        Amount BIGINT
    );

    BEGIN TRANSACTION;

    -- Archive transactions
    DELETE Transactions WITH (TABLOCK)
    OUTPUT deleted.CustomerId, deleted.TotalAmount INTO @CustomerBalance
    WHERE [TimeStamp] < @ArchiveDateTime;

    IF EXISTS (SELECT 1 FROM @CustomerBalance)
    BEGIN
        UPDATE Customers SET StartingBalance = StartingBalance +
            (SELECT SUM(Amount) FROM @CustomerBalance cb WHERE Id = cb.CustomerId)
    END;

    DELETE FROM @CustomerBalance

    -- Archive payments
    DELETE Payments WITH (TABLOCK)
    OUTPUT deleted.CustomerId, deleted.Amount INTO @CustomerBalance
    WHERE [Date] < @ArchiveDateTime;

    IF EXISTS (SELECT 1 FROM @CustomerBalance)
    BEGIN
        UPDATE Customers SET StartingBalance = StartingBalance -
            (SELECT SUM(Amount) FROM @CustomerBalance cb WHERE Id = cb.CustomerId)
    END;

    -- Probably not needed
    DELETE FROM @CustomerBalance

    COMMIT TRANSACTION;
END

由于 SQL 不是我的核心能力，因此我想获得对此的反馈。看起来“正确”吗？看起来是不是很理想？另外，我不确定以下条款。

UPDATE Customers SET StartingBalance = StartingBalance -
    (SELECT SUM(Amount) FROM @CustomerBalance cb WHERE Id = cb.CustomerId)

  1. @CustomerBalance在不包含客户行的情况下，这会做什么？

  2. 这在@CustomerBalance为客户包含多行的情况下做什么？

感谢您的任何建议。

标签： sqlsql-servertsql

解决方案

关于你的最后一个问题

-- i would add the name of the outer query table in the inner one
UPDATE Customers 
SET StartingBalance = StartingBalance -  (SELECT SUM(Amount) 
                                          FROM @CustomerBalance cb 
                                          WHERE Customers.Id = cb.CustomerId);

当您使用聚合函数时，内部查询将为您提供为客户端找到的所有行的总和。如果没有找到行，则为 0。

推荐阅读