sql-server - 对选择结果中的每一行执行 INSERT

Question

首先，对问题进行一般性描述:我正在运行一个周期性流程来更新表格中的总数字。问题是，在流程的每次执行中可能需要多次更新，并且每次执行都取决于之前的结果。

我的问题是，它可以在单个 SQL Server SP 中完成吗？

我的代码(我稍微修改了一下以简化示例):

INSERT INTO CustomerMinuteSessions(time, customer, sessions, bytes, previousTotalSessions)
SELECT MS.time,
           MS.customer,
           MS.totalSessions,
           MS.totalBytes,
           CTS.previousTotalSessions
FROM (SELECT time, customer, SUM(sessions) as totalSessions, SUM(bytes) AS totalBytes
      FROM MinuteSessions
      WHERE time > @time
      GROUP BY time, x) MS
CROSS APPLY TVF_GetPreviousCustomerTotalSessions(MS.customer) CTS
ORDER BY time

previousTotalSessions 列依赖于 UpdatedTable 中的其他行，其值通过 CROSS APPLYing TVF_GetPreviousCustomerTotalSessions 检索>，但如果我按原样执行 SP，所有行都使用函数检索的值，而不使用 在 SP 执行期间添加的行。

为了完整起见，这里是 TVF_GetPreviousCustomerTotalSessions:

FUNCTION [dbo].[TVF_GetCustomerCurrentSessions] 
(   
    @customerId int
)
RETURNS @result TABLE (PreviousNumberOfSessions int)
AS
BEGIN
    INSERT INTO @result
    SELECT TOP 1 (PreviousNumberOfSessions + Opened - Closed) AS PreviousNumberOfSessions
    FROM CustomerMinuteSessions 
    WHERE CustomerId = @customerId 
    ORDER BY time DESC

    IF @@rowcount = 0
        INSERT INTO @result(PreviousNumberOfSessions) VALUES(0)

    RETURN
END

什么是最好的(即没有 for 循环，我猜......)在后续行的查询中获取先前的行？

Answer 1

如果您使用的是 SQL-2005 及更高版本，则只需很少的 CTE 即可一次性完成。如果您使用 SQL-2000，则可以使用内联表值函数。

我个人更喜欢 CTE 方法，因此我将您的代码转换为 CTE 语法的示意图。 (切记，我没有准备测试集来检查它)。

WITH LastSessionByCustomer AS  
(
    SELECT CustomerID, MAX(Time)
    FROM CustomerMinuteSessions
    GROUP BY CustomerID
)
, GetPreviousCustomerTotalSessions AS
(
    SELECT LastSession.CustomerID, LastSession.PreviousNumberOfSessions + LastSession.Opened - LastSession.Closed AS PreviousNumberOfSessions
    FROM CustomerMinuteSessions LastSession
    INNER JOIN LastSessionByCustomer ON LastSessionByCustomer.CustomerID = LastSession.CustomerID
)
, MS AS
(
    SELECT time, customer, SUM(sessions) as totalSessions, SUM(bytes) AS totalBytes
    FROM MinuteSessions
    WHERE time > @time
    GROUP BY time, x
)
INSERT INTO CustomerMinuteSessions(time, customer, sessions, bytes, previousTotalSessions)
SELECT MS.time,
           MS.customer,
           MS.totalSessions,
           MS.totalBytes,
           ISNULL(GetPreviousCustomerTotalSessions.previousTotalSessions, 0)
FROM MS 
RIGHT JOIN GetPreviousCustomerTotalSessions ON MS.Customer = GetPreviousCustomerTotalSessions.CustomerID

有点超出你的问题，我认为一旦表 CustomerMinuteSessions 数据库增长，你的交叉应用查询可能会对数据库造成重大损害我会添加一个索引，以提高您获得 Index-Seek 的机会:

CREATE INDEX IX_CustomerMinuteSessions_CustomerId
    ON CustomerMinuteSessions (CustomerId, [time] DESC, PreviousNumberOfSessions, Opened, Closed );