如何修改存储过程提取自配置天数后未交易的活跃用户？

原存储过程的逻辑是筛选最近N天内有成功交易的活跃用户，要改成获取最近N天内没有成功交易的活跃用户，核心是反转交易记录的判断逻辑，以下是两种可行的修改方案：

方案一：使用NOT EXISTS子查询（推荐，性能通常更优） #

直接检查用户在指定天数内不存在符合条件的成功交易，逻辑清晰且效率较高：

CREATE PROCEDURE [ContentPush].[GetLastVisitDateTransaction]
    @DaysSinceLastVisit              INT,
    @TenantID                        UNIQUEIDENTIFIER
AS
BEGIN
DECLARE @ReturnJson NVARCHAR(MAX)
SET @ReturnJson = (
            SELECT DISTINCT [D].[UserID]
            FROM [dbo].[UserInfo] D WITH(NOLOCK)
            WHERE
                D.IsActive = 1
                -- 若UserInfo表本身有租户ID字段，直接在此过滤更高效
                -- AND D.AppTenantID = @TenantID
                AND NOT EXISTS (
                    SELECT 1
                    FROM [Txn].[Txn] T WITH(NOLOCK)
                    INNER JOIN [Txn].[TxnPaymentResponse] TPR WITH(NOLOCK) ON [T].[TxnID] = [TPR].[TxnID]
                    WHERE
                        T.UserID = D.UserID
                        AND TPR.PaymentResponseType = 'FINAL'
                        AND TPR.PaymentResultCode = 'approved'
                        AND T.AppTenantID = @TenantID
                        AND T.TransactionDateTime >= DATEADD(DD, -@DaysSinceLastVisit, GETUTCDATE())
                )
                FOR JSON PATH)
SELECT @ReturnJson
END

方案二：使用LEFT JOIN + NULL判断 #

通过左连接交易表，筛选出没有匹配到最近成功交易记录的用户：

CREATE PROCEDURE [ContentPush].[GetLastVisitDateTransaction]
    @DaysSinceLastVisit              INT,
    @TenantID                        UNIQUEIDENTIFIER
AS
BEGIN
DECLARE @ReturnJson NVARCHAR(MAX)
SET @ReturnJson = (
            SELECT DISTINCT [D].[UserID]
            FROM [dbo].[UserInfo] D WITH(NOLOCK)
            LEFT JOIN (
                SELECT T.UserID
                FROM [Txn].[Txn] T WITH(NOLOCK)
                INNER JOIN [Txn].[TxnPaymentResponse] TPR WITH(NOLOCK) ON [T].[TxnID] = [TPR].[TxnID]
                WHERE
                    TPR.PaymentResponseType = 'FINAL'
                    AND TPR.PaymentResultCode = 'approved'
                    AND T.AppTenantID = @TenantID
                    AND T.TransactionDateTime >= DATEADD(DD, -@DaysSinceLastVisit, GETUTCDATE())
            ) AS RecentTxn ON D.UserID = RecentTxn.UserID
            WHERE
                D.IsActive = 1
                -- 若UserInfo表本身有租户ID字段，直接在此过滤更高效
                -- AND D.AppTenantID = @TenantID
                AND RecentTxn.UserID IS NULL
                FOR JSON PATH)
SELECT @ReturnJson
END

关键改动说明 #

1. 反转交易判断逻辑：从原来的INNER JOIN（仅保留有交易的用户）改为检查交易记录不存在的逻辑
2. 保留核心过滤条件：依然只针对活跃用户（D.IsActive = 1）、指定租户，且仅排除已完成且审批通过的交易（FINAL + approved）
3. 租户关联调整：如果UserInfo表本身有租户ID字段，直接在主表过滤租户会更高效；如果没有，可通过关联历史交易表确保筛选的是该租户下的用户（参考下方补充示例）

补充：UserInfo无租户ID的场景 #

如果UserInfo表没有租户ID字段，只能获取该租户下曾经有过交易，但最近N天没有成功交易的用户，调整后的代码如下：

CREATE PROCEDURE [ContentPush].[GetLastVisitDateTransaction]
    @DaysSinceLastVisit              INT,
    @TenantID                        UNIQUEIDENTIFIER
AS
BEGIN
DECLARE @ReturnJson NVARCHAR(MAX)
SET @ReturnJson = (
            SELECT DISTINCT [D].[UserID]
            FROM [dbo].[UserInfo] D WITH(NOLOCK)
            -- 先关联该租户的历史交易，确保是租户内的用户
            INNER JOIN [Txn].[Txn] T_All WITH(NOLOCK) ON D.UserID = T_All.UserID
            WHERE
                D.IsActive = 1
                AND T_All.AppTenantID = @TenantID
                AND NOT EXISTS (
                    SELECT 1
                    FROM [Txn].[Txn] T WITH(NOLOCK)
                    INNER JOIN [Txn].[TxnPaymentResponse] TPR WITH(NOLOCK) ON [T].[TxnID] = [TPR].[TxnID]
                    WHERE
                        T.UserID = D.UserID
                        AND TPR.PaymentResponseType = 'FINAL'
                        AND TPR.PaymentResultCode = 'approved'
                        AND T.AppTenantID = @TenantID
                        AND T.TransactionDateTime >= DATEADD(DD, -@DaysSinceLastVisit, GETUTCDATE())
                )
                FOR JSON PATH)
SELECT @ReturnJson
END

内容的提问来源于stack exchange，提问作者duckgame