如何在子查詢中參考系統版本表-有解無憂

我有一個系統版本的聯系人臨時表。我還有一張使用聯系人時的事件表。我想匯總并獲取事件發生時串列中可用聯系人的計數。

為此，我正在使用子查詢。但是當我嘗試指定相應的事件時間時，SQL Server Management Studio 不會編譯查詢。

如何在具有非硬編碼時間戳的子查詢中參考系統版本表？

DECLARE @CurrentTime DATETIMEOFFSET = GETUTCDATE()
DECLARE @LastSentTime DATETIMEOFFSET = DATEADD(day, -28, @CurrentTime)

SELECT
    c.ContactListID,
    ( SELECT COUNT( DISTINCT con.Email )
      FROM [schema].[Contact] FOR SYSTEM_TIME AS OF c.StartDate AS con
      WHERE c.ContactListID = con.ContactListID) as [ContactCount],
    c.StartDate
INTO #TEMP
FROM [schema].[event] c
WHERE StatusID = 3
AND StartDate > @LastSentTime
AND StartDate <= @CurrentTime
ORDER BY StartDate ASC

FOR SYSTEM_TIME AS OF c.StartDate AS con 不起作用，參考不正確的語法

但是如果我硬編碼日期字串

FOR SYSTEM_TIME AS OF '2022-01-03 23:00:00.00 00:00' AS con

它確實

uj5u.com熱心網友回復：

查看檔案，引數 forAS OF必須是文字或變數；它不支持在外部查詢中使用列中的值。

FROM 子句加上 JOIN、APPLY、PIVOT (T-SQL) - SQL Server | 微軟檔案

<system_time> ::=  
{  
      AS OF <date_time>  
   |  FROM <start_date_time> TO <end_date_time>  
   |  BETWEEN <start_date_time> AND <end_date_time>  
   |  CONTAINED IN (<start_date_time> , <end_date_time>)   
   |  ALL  
}  
 
<date_time>::=  
    <date_time_literal> | @date_time_variable  
 
<start_date_time>::=  
    <date_time_literal> | @date_time_variable  
 
<end_date_time>::=  
    <date_time_literal> | @date_time_variable

您需要使用標量值函式來計算歷史記錄行數：

CREATE OR ALTER FUNCTION [schema].CountEmailContacts
(
    @ContactListID int,
    @AsOf datetime2(0)
)
Returns int
As
BEGIN
    Return (SELECT COUNT(DISTINCT Email)
    FROM [schema].[Contact]
    FOR SYSTEM_TIME AS OF @AsOf
    WHERE ContactListID = @ContactListID);
END;

用法：

SELECT
    c.ContactListID,
    [schema].CountEmailContacts(c.ContactListID, c.StartDate) As [ContactCount],
    c.StartDate
INTO #TEMP
FROM [schema].[event] c
WHERE StatusID = 3
AND StartDate > @LastSentTime
AND StartDate <= @CurrentTime
ORDER BY StartDate ASC

編輯：
或者，正如 Stu 指出的，一個行內表值函式，它應該有更好的性能：

CREATE OR ALTER FUNCTION [schema].CountEmailContacts
(
    @ContactListID int,
    @AsOf datetime2(0)
)
Returns Table
As
Return 
(
    SELECT COUNT(DISTINCT Email) As ContactCount
    FROM [schema].[Contact]
    FOR SYSTEM_TIME AS OF @AsOf
    WHERE ContactListID = @ContactListID
);

用法：

SELECT
    c.ContactListID,
    con.[ContactCount],
    c.StartDate
INTO #TEMP
FROM [schema].[event] c
CROSS APPLY [schema].CountEmailContacts(c.ContactListID, c.StartDate) con
WHERE StatusID = 3
AND StartDate > @LastSentTime
AND StartDate <= @CurrentTime
ORDER BY StartDate ASC

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/422049.html

標籤：

上一篇：如何從SQL中的列中洗掉羅馬字母和數值

下一篇：SQLServer-優化查詢