sql - 在 SQL Server 中使用带有 partition by by 的 Case 语句

Question

嗨，我正在尝试从此代码中获取以下内容如果有多个匹配项，请找到 LearnStartDate = LearnDelFAMDateFrom 的记录，否则留空

select 
ukprn, aimseqnumber, LearnRefNumber
,LearnDelFAMCode
,LearnDelFAMType
,LearnDelFAMDateFrom
,LearningStartDate
,CASE
        when rn=1 then LearnDelFAMDateFrom
        when rn <1 then ''
        when (rn =1 and rn > 1) and (LearningStartDate = LearnDelFAMDateFrom) then LearnDelFAMDateFrom
        else
        ''
        end as LearnDelFAMDateFromMOD

--,CASE row_number() over(partition by d.LearnRefNumber,d.aimseqnumber order by LearnDelFAMDateFrom)  
-- when  1  then LearnDelFAMDateFrom
-- when < 1 then '' 
-- when > 1 and (LearningStartDate = LearnDelFAMDateFrom) then LearnDelFAMDateFrom
-- else ''
--   end as     LearnDelFAMDateFromMOD2

,rn 
--,LearnDelFAMDateTo


from
(select 
     d.ukprn,d.aimseqnumber,d.LearnRefNumber,LearnDelFAMCode,LearnDelFAMType
    ,convert(varchar(10),LearnDelFAMDateFrom) as LearnDelFAMDateFrom
    ,LearnDelFAMDateTo,LearningStartDate
    ,row_number() over(partition by d.LearnRefNumber,d.aimseqnumber order by LearnDelFAMDateFrom) rn 

    from  d

    left join rp 
    on

        d.ukprn =rp.ukprn
    and d.aimseqnumber=rp.AimSeqNumber
    and d.LearnRefNumber=rp.LearnRefNumber
    and LearnDelFAMCode=rp.ApprenticeshipContractType

    left join   aecld

   on
        d.ukprn =aecld.ukprn
  and   d.aimseqnumber=aecld.AimSeqNumber
  and   d.LearnRefNumber=aecld.learnrefnumber


    where LearnDelFAMType='ACT' 

        ) as ref

由于我不能使用大于或小于或等号，因此无法运行包含分区大小写的代码的注释部分。

如果我只是在 (rn =1 and rn > 1) 和 (LearningStartDate = LearnDelFAMDateFrom) 的情况下使用 case 语句，那么 LearnDelFAMDateFrom 这仅适用于当 rn = 1 时 rn >1 时的记录，它不起作用。

标签： sqlsql-servertsql

Answer 1

我建议为此任务使用 CTE 表，ROW_NUMBER()... AS rn 将被解析，然后您可以在您的案例语句中使用它

;WITH CTE_TEMP_TABLENAME AS 
(
select 
 d.ukprn,d.aimseqnumber,d.LearnRefNumber,LearnDelFAMCode,LearnDelFAMType
,convert(varchar(10),LearnDelFAMDateFrom) as LearnDelFAMDateFrom
,LearnDelFAMDateTo,LearningStartDate
,row_number() over(partition by d.LearnRefNumber,d.aimseqnumber order by LearnDelFAMDateFrom) rn 

from  d

left join rp 
on

    d.ukprn =rp.ukprn
and d.aimseqnumber=rp.AimSeqNumber
and d.LearnRefNumber=rp.LearnRefNumber
and LearnDelFAMCode=rp.ApprenticeshipContractType

left join   aecld

on
    d.ukprn =aecld.ukprn
and   d.aimseqnumber=aecld.AimSeqNumber
and   d.LearnRefNumber=aecld.learnrefnumber


where LearnDelFAMType='ACT' 
)

select 
  ukprn
 ,aimseqnumber
 ,LearnRefNumber
 ,LearnDelFAMCode
 ,LearnDelFAMType
 ,LearnDelFAMDateFrom
 ,LearningStartDate
 ,CASE
    when rn=1 then LearnDelFAMDateFrom
    when rn <1 then ''
    when (rn =1 and rn > 1) and (LearningStartDate = LearnDelFAMDateFrom) 
 then LearnDelFAMDateFrom
    else
    ''
    end as LearnDelFAMDateFromMOD
,rn 
,LearnDelFAMDateTo
FROM CTE_TEMP_TABLENAME