在同一表的不同列中查找匹配的记录

Question

我有这样的疑问：

SELECT
    TLOCMTR.LOCATN_K, TLOCMTR.MTR_SRP_N, TLOCMTR.MTR_INST_D, 
    TLOCMTR.MTR_XCHG_OUT_D, RATE.RATE, TMTR.MTR_MFR_MOD_C, 
    TMTR.MTR_MFR_NM_CD
FROM
    TMTR 
RIGHT OUTER JOIN
    TLOCMTR ON TMTR.MTR_SRP_N = TLOCMTR.MTR_SRP_N 
LEFT OUTER JOIN
    RATE ON TLOCMTR.LOCATN_K = RATE.LOCATN_K
WHERE
    ((RATE.RATE >= 20) AND (RATE.RATE <= 29)) 
    AND TLOCMTR.MTR_INST_D >= '2014-01-01' 
ORDER BY
    LOCATN_K, MTR_INST_D

代码的结果是：

​

​

我需要做的是计算每个Locatn_K的MTR_MFR_NM_CD从AMP更改为<>AMP的次数，其中MTR_INST_D = MTR_XCHG_OUT_D。例如，LOCATN_K = 85420005从AMP更改为EEM，MTR_XCHG_D = MTR_INST_D 1次(日期为'2014-08-27‘。LOCATN_K 85430001在2015-01-05和2016-01-12两次从AMP转换为EEM。

请用一些T-SQL来帮助我回答这个问题！

Answer 1

这就是要点

select * 
  from ( select LOCATN_K, MTR_INST_D, MTR_MFR_NM_CD 
              , LEAD(MTR_MFR_NM_CD, 1,0) OVER (PARTITTION LOCATN_K BY ORDER BY MTR_INST_D) AS MTR_MFR_NM_CD_NEXT
           from 
           ...
        ) tt
 where MTR_MFR_NM_CD      =  'AMP'
   and MTR_MFR_NM_CD_NEXT <> 'AMP'

Answer 2

我尝试了飞盘的代码，做了以下修改，但没有得到确切的结果：

select * 
from ( select TLOCMTR.LOCATN_K, TLOCMTR.MTR_SRP_N, TLOCMTR.MTR_INST_D, TLOCMTR.MTR_XCHG_OUT_D, TMTR.MTR_MFR_MOD_C, TMTR.MTR_MFR_NM_CD,
LEAD(TMTR.MTR_MFR_NM_CD, 1,0) OVER (PARTITION BY TLOCMTR.LOCATN_K  
ORDER BY MTR_INST_D) AS MTR_MFR_NM_CD_NEXT
FROM TMTR RIGHT OUTER JOIN TLOCMTR ON TMTR.MTR_SRP_N = TLOCMTR.MTR_SRP_N 
LEFT OUTER JOIN RATE ON TLOCMTR.LOCATN_K = RATE.LOCATN_K
WHERE ((RATE.RATE >= 20) AND (RATE.RATE <= 29)) AND TLOCMTR.MTR_INST_D>='2014-01-01' AND TMTR.MTR_MFR_NM_CD = 'AMP'      
) tt
where tt.MTR_MFR_NM_CD = 'AMP'
and tt.MTR_MFR_NM_CD_NEXT <> 'AMP'
order by tt.LOCATN_K

这导致了'Amp‘到'Amp’和<>'Amp‘到'Amp'，但我只想得到’Amp‘到<>'Amp’。

对我来说，最简单的方法是：

WITH A AS 
(SELECT  TLOCMTR.LOCATN_K, TLOCMTR.MTR_SRP_N, TLOCMTR.MTR_XCHG_OUT_D, TMTR.MTR_MFR_MOD_C, TMTR.MTR_MFR_NM_CD
FROM  TMTR RIGHT OUTER JOIN TLOCMTR ON TMTR.MTR_SRP_N = TLOCMTR.MTR_SRP_N LEFT OUTER JOIN  RATE ON TLOCMTR.LOCATN_K = RATE.LOCATN_K
WHERE ((RATE.RATE >= 20) AND (RATE.RATE <= 29)) and TLOCMTR.MTR_INST_D>='2014-01-01' AND TMTR.MTR_MFR_NM_CD = 'AMP'
),
B AS(
SELECT TLOCMTR.LOCATN_K, TLOCMTR.MTR_SRP_N, TLOCMTR.MTR_INST_D, TMTR.MTR_MFR_MOD_C, TMTR.MTR_MFR_NM_CD
FROM TMTR RIGHT OUTER JOIN TLOCMTR ON TMTR.MTR_SRP_N = TLOCMTR.MTR_SRP_N 
LEFT OUTER JOIN RATE ON TLOCMTR.LOCATN_K = RATE.LOCATN_K
WHERE ((RATE.RATE >= 20) AND (RATE.RATE <= 29)) AND TLOCMTR.MTR_INST_D>='2014-01-01' AND TMTR.MTR_MFR_NM_CD <> 'AMP' 
)

SELECT A.LOCATN_K, A.MTR_SRP_N AmpyMeter, B.MTR_SRP_N CreditMeter, B.MTR_INST_D EXCG_DT,  A.MTR_MFR_MOD_C AMP_MODEL, B.MTR_MFR_MOD_C Credit_MODEL
FROM A inner join B on A.MTR_XCHG_OUT_D = B.MTR_INST_D and A.LOCATN_K=B.LOCATN_K
ORDER BY LOCATN_K

我的最终结果如下所示：

​