不同日期Select查询中新增记录的对比与识别方案

问题描述 #

我有一段存储过程中的查询语句，结果是正确的。现在需要添加逻辑，把当前查询结果和1天前运行同一查询的结果对比，把未出现在1天前结果中的新增记录放在结果集最顶部。我尝试用EXCEPT子句但结果不对，求帮忙识别这些新增记录。

原查询代码 #

SELECT  distinct rt.sPortfolio, CAST(rt.iRuleId AS VARCHAR(15)) AS iRuleId, sDescription, sComment, rt.sPortfolio AS BlankRow
FROM thinktank..ruletests AS rt
   INNER JOIN thinktank..rules AS r
   ON r.id = rt.iruleid
     AND dttest > getdate()-0.5
     AND iresult <> 4
     AND ichecktype = 3
     AND r.iCategory = 0
     AND sRuleSet <> 'NOTIFY'
   INNER JOIN thinktank..GroupPortfolios AS gp
   ON rt.sPortfolio = gp.sPortfolioId
     AND sGroupID = 'EC'
   LEFT OUTER JOIN thinktank..RuleUCs AS ruc
   ON r.id = ruc.iruleid 
     AND ruc.icategory = '636'
   WHERE ISNULL(ruc.sValue,'N') <> 'Y'

EXCEPT

SELECT  distinct rt.sPortfolio, CAST(rt.iRuleId AS VARCHAR(15)) AS iRuleId, sDescription, sComment, rt.sPortfolio AS BlankRow
FROM thinktank..ruletests AS rt
    INNER JOIN thinktank..rules AS r
    ON r.id = rt.iruleid
      AND dttest > getdate()-1
      AND iresult <> 4
      AND ichecktype = 3
      AND r.iCategory = 0
      AND sRuleSet <> 'NOTIFY'
    INNER JOIN thinktank..GroupPortfolios AS gp
    ON rt.sPortfolio = gp.sPortfolioId
      AND sGroupID = 'EC'
    LEFT OUTER JOIN thinktank..RuleUCs AS ruc
    ON r.id = ruc.iruleid 
      AND ruc.icategory = '636'
    WHERE ISNULL(ruc.sValue,'N') <> 'Y'

原代码问题分析 #

你用EXCEPT得到空结果或错误结果的核心原因：

• 第一个查询取的是**最近12小时（getdate()-0.5）**的记录
• 第二个查询取的是**最近24小时（getdate()-1）**的记录
• 最近12小时的记录完全包含在最近24小时的范围内，所以EXCEPT后结果为空，完全不符合你找新增记录的需求。

正确解决方案 #

要实现需求，需要明确“1天前的查询结果”对应的时间段，用CTE封装两个结果集后标记新增记录，最后按标记排序。

完整SQL代码 #

WITH CurrentResults AS (
    -- 当前查询：最近12小时的符合条件记录
    SELECT DISTINCT 
        rt.sPortfolio, 
        CAST(rt.iRuleId AS VARCHAR(15)) AS iRuleId, 
        sDescription, 
        sComment, 
        rt.sPortfolio AS BlankRow
    FROM thinktank..ruletests AS rt
    INNER JOIN thinktank..rules AS r
        ON r.id = rt.iruleid
        AND rt.dttest > GETDATE()-0.5
        AND rt.iresult <> 4
        AND rt.ichecktype = 3
        AND r.iCategory = 0
        AND r.sRuleSet <> 'NOTIFY'
    INNER JOIN thinktank..GroupPortfolios AS gp
        ON rt.sPortfolio = gp.sPortfolioId
        AND gp.sGroupID = 'EC'
    LEFT OUTER JOIN thinktank..RuleUCs AS ruc
        ON r.id = ruc.iruleid 
        AND ruc.icategory = '636'
    WHERE ISNULL(ruc.sValue,'N') <> 'Y'
),
HistoricalResults AS (
    -- 1天前的查询结果：对应1天前的12小时窗口
    SELECT DISTINCT 
        rt.sPortfolio, 
        CAST(rt.iRuleId AS VARCHAR(15)) AS iRuleId
    FROM thinktank..ruletests AS rt
    INNER JOIN thinktank..rules AS r
        ON r.id = rt.iruleid
        AND rt.dttest BETWEEN GETDATE()-1.5 AND GETDATE()-1
        AND rt.iresult <> 4
        AND rt.ichecktype = 3
        AND r.iCategory = 0
        AND r.sRuleSet <> 'NOTIFY'
    INNER JOIN thinktank..GroupPortfolios AS gp
        ON rt.sPortfolio = gp.sPortfolioId
        AND gp.sGroupID = 'EC'
    LEFT OUTER JOIN thinktank..RuleUCs AS ruc
        ON r.id = ruc.iruleid 
        AND ruc.icategory = '636'
    WHERE ISNULL(ruc.sValue,'N') <> 'Y'
)
-- 合并结果，标记新增记录并排序
SELECT 
    cr.*,
    CASE WHEN hr.sPortfolio IS NULL THEN 1 ELSE 0 END AS IsNew
FROM CurrentResults cr
LEFT JOIN HistoricalResults hr
    ON cr.sPortfolio = hr.sPortfolio
    AND cr.iRuleId = hr.iRuleId
ORDER BY IsNew DESC, cr.sPortfolio, cr.iRuleId;

代码说明 #

1. CurrentResults：保留你原有的当前查询逻辑，获取最近12小时的有效记录。
2. HistoricalResults：只保留用于匹配的唯一键（sPortfolio和iRuleId），无需其他字段，提升匹配效率。
3. 新增标记：通过左连接判断记录是否存在于历史结果中，IsNew=1表示是新增记录，0表示原有记录。
4. 排序逻辑：先按IsNew降序，确保新增记录排在最顶部，后续可根据业务需求添加其他排序字段。

如果你的“上次运行”指的是截止到1天前的所有符合条件记录，只需修改HistoricalResults的时间条件为rt.dttest <= GETDATE()-1即可。

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