首先,对问题进行一般性描述:我正在运行一个周期性流程来更新表格中的总数字。问题是,在流程的每次执行中可能需要多次更新,并且每次执行都取决于之前的结果。
我的问题是,它可以在单个 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 APPLY
ing 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 循环,我猜......)在后续行的查询中获取先前的行?
最佳答案
如果您使用的是 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 );
https://stackoverflow.com/questions/17383660/