如何基于含两列的子查询排除SQL中的指定交易记录？

要得到338条未被取消的有效SELL记录，核心是排除那些存在对应CSELL取消记录的SELL，同时过滤掉所有CSELL类型的交易，以下是两种可靠的写法：

方法一：使用NOT EXISTS（修正版） #

SELECT     
    [FundID]
    ,[AssetID]
    ,[TransactionTypeCode]
    ,[TradeDate]
    ,[NetAmountBase]
    ,[BrokerNumber]
    ,[TradeID]
    ,[TradeIDCancel]
FROM [DailyTradeTransactions] WITH (NOLOCK) dtt
WHERE TransactionTypeCode = 'SELL'  -- 只保留初始SELL交易，CSELL是取消记录无需保留
    AND TradeDate BETWEEN @StartDate AND @EndDate
    AND BrokerNumber NOT IN ('12345','23456','34567','45678','56789','67891')
    AND TradeIDCancel IS NULL  -- 限定是未标记取消的初始SELL
    AND NOT EXISTS (
        SELECT 1
        FROM [DailyTradeTransactions] WITH (NOLOCK) cancel
        WHERE cancel.TransactionTypeCode = 'CSELL'
            AND cancel.TradeIDCancel = dtt.TradeID  -- 匹配对应SELL的取消记录
            AND cancel.TradeDate BETWEEN @StartDate AND @EndDate  -- 确保取消记录在查询时间范围内
    )

方法二：使用LEFT JOIN过滤 #

如果对NOT EXISTS逻辑不熟，用LEFT JOIN也能实现相同效果：

SELECT     
    dtt.[FundID]
    ,dtt.[AssetID]
    ,dtt.[TransactionTypeCode]
    ,dtt.[TradeDate]
    ,dtt.[NetAmountBase]
    ,dtt.[BrokerNumber]
    ,dtt.[TradeID]
    ,dtt.[TradeIDCancel]
FROM [DailyTradeTransactions] WITH (NOLOCK) dtt
LEFT JOIN [DailyTradeTransactions] WITH (NOLOCK) cancel
    ON cancel.TransactionTypeCode = 'CSELL'
    AND cancel.TradeIDCancel = dtt.TradeID
    AND cancel.TradeDate BETWEEN @StartDate AND @EndDate
WHERE dtt.TransactionTypeCode = 'SELL'
    AND dtt.TradeDate BETWEEN @StartDate AND @EndDate
    AND dtt.BrokerNumber NOT IN ('12345','23456','34567','45678','56789','67891')
    AND dtt.TradeIDCancel IS NULL
    AND cancel.TradeID IS NULL  -- 没有找到对应的CSELL取消记录

为什么你之前的NOT EXISTS没生效？ #

大概率是子查询没做这两个关键限制：

• 没限定取消记录的TransactionTypeCode = 'CSELL'，导致匹配到其他无关交易
• 没加时间范围过滤，匹配到了查询区间外的取消记录，错误排除了有效SELL

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