SQL查询，返回包含更改的数据

Question

我只想隔离从'900‘更改为'RCF’的源键值。我的代码现在返回所有记录，而不考虑任何更改。

select 

PD_END_DT, 
SOURCE_KEY_VALUE, 
Fed_Class_cd

from XXXXXXXXXXXX 
where (pd_end_dt = '2015-11-30' or PD_END_DT = '2015-12-31')
    and (FED_CLASS_CD = '900' or FED_CLASS_CD = 'RCF')

示例数据：

 pd_ned_dt      Source_key_value        Fed_Cls_Cd

2015-11-30 CML0100000565101565000000000  900
2015-11-30 CML0100000613059250000613000  900
2015-12-31 CML0100000613059250000613000  RCF
2015-12-31 CML0100000613060250000613000  RCF
2015-11-30 CML0100000613060250000613000  900
2015-11-30 CML0100000613061250000613000  900
2015-12-31 CML0100000613061250000613000  RCF
2015-12-31 CML0100000613062250000613000  RCF
2015-11-30 CML0100000613062250000613000  900
2015-11-30 CML0100000633001633000000000  900
2015-12-31 CML0100000633001633000000000  900
2015-12-31 CML0100000641001641000000000  RCF
2015-11-30 CML0100000641001641000000000  900
2015-11-30 CML0100000641002641000000000  900
2015-12-31 CML0100000641002641000000000  RCF
2015-12-31 CML0100000641003641000000000  RCF

我可以使用什么查询来仅显示我想要查看的记录？谢谢你的帮助！

Answer 1

试试这个。

IF OBJECT_ID('tempdb..#TestData', 'U') IS NULL 
BEGIN   -- DROP TABLE #TestData;
    CREATE TABLE #TestData (
         pd_ned_dt DATE NOT NULL,
         Source_key_value CHAR(28) NOT NULL,
         Fed_Cls_Cd CHAR(3) NOT NULL 
        );
    INSERT #TestData (pd_ned_dt, Source_key_value, Fed_Cls_Cd) VALUES
        ('2015-11-30', 'CML0100000565101565000000000', '900'),
        ('2015-11-30', 'CML0100000613059250000613000', '900'),
        ('2015-12-31', 'CML0100000613059250000613000', 'RCF'),
        ('2015-12-31', 'CML0100000613060250000613000', 'RCF'),
        ('2015-11-30', 'CML0100000613060250000613000', '900'),
        ('2015-11-30', 'CML0100000613061250000613000', '900'),
        ('2015-12-31', 'CML0100000613061250000613000', 'RCF'),
        ('2015-12-31', 'CML0100000613062250000613000', 'RCF'),
        ('2015-11-30', 'CML0100000613062250000613000', '900'),
        ('2015-11-30', 'CML0100000633001633000000000', '900'),
        ('2015-12-31', 'CML0100000633001633000000000', '900'),
        ('2015-12-31', 'CML0100000641001641000000000', 'RCF'),
        ('2015-11-30', 'CML0100000641001641000000000', '900'),
        ('2015-11-30', 'CML0100000641002641000000000', '900'),
        ('2015-12-31', 'CML0100000641002641000000000', 'RCF'),
        ('2015-12-31', 'CML0100000641003641000000000', 'RCF');
END;

--=============================================================

SELECT 
    ChangeDate = td1.pd_ned_dt,
    td1.Source_key_value,
    BeforeChange = td2.Fed_Cls_Cd,
    AfterChange = td1.Fed_Cls_Cd
FROM 
    #TestData td1
    JOIN #TestData td2
        ON td1.Source_key_value = td2.Source_key_value
WHERE 
    td1.Fed_Cls_Cd = 'RCF'
    AND td1.pd_ned_dt >= '2015-11-30'
    AND td1.pd_ned_dt < '2016-01-01'
    AND td2.Fed_Cls_Cd = '900'
    AND td1.pd_ned_dt > td2.pd_ned_dt;

Answer 2

select distinct pd_ned_dt , Source_key_value  ,  Fed_Cls_Cd from xxx As a 
where   
Exists(select Source_key_value from xxx where Source_key_value=a.Source_key_value and Fed_Cls_Cd='900')

Answer 3

这个怎么样？

SELECT *
FROM #XXXXXXXXXXXX q900
    INNER JOIN #XXXXXXXXXXXX qRCF
        ON q900.SOURCE_KEY_VALUE = qRCF.SOURCE_KEY_VALUE    
        AND q900.pd_end_dt < qRCF.pd_end_dt
        AND q900.FED_CLASS_CD = '900' 
        AND qRCF.FED_CLASS_CD = 'RCF' 

注意:如果RCF一点都不相关，你需要一些其他的方法来知道针对该键的PD_END_DT记录比900记录新。是否有其他字段可以用于此目的？