sql - 如何根据单个列值有条件地限制结果

Question

我在大量使用行版本控制的第 3 方数据库中工作。通常这不是问题，但对于特定的数据集，这是由于前端的松散业务规则造成的。您会注意到，INNER JOIN 用于为每个 EmpID 获取具有最新 EffectiveDate 的行组。应始终使用此逻辑，除非有一行 EnrollmentStatus = 'Enrolled'。如果存在，则应返回这组行。我所说的行组是指 EmpID 和 EffectiveDate。

在下面的数据集中，所需结果将是 EffectiveDate = '2015-12-15' 的 4 行，因为它包含 EnrollmentStatus = 'Enrolled'。如果每个 EmpID 的记录都没有 EnrollmentStatus = 'Enrolled'，则内部联接就足够了。

我确定我忽略了执行此操作的优雅方式。

if object_id('tempdb..#emp') is not null drop table #emp

create table #emp
    (EmpID int, 
    EmpBenID int,
    EffectiveDate datetime,
    EligibilityDate datetime,
    EnrollBeginDate datetime,
    OverrideEnrollBeginDate datetime,
    EnrollEndDate datetime,
    OverrrideEnrollEndDate datetime,
    EnrollStatus varchar(64))

insert into #emp(EmpID, EmpBenID, EffectiveDate, EligibilityDate, EnrollBeginDate,OverrideEnrollBeginDate,EnrollEndDate,OverrrideEnrollEndDate,EnrollStatus) 
values
(1950,55403,'2015-12-15 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled'),
(1950,55404,'2015-12-15 00:00:00','1998-11-02 00:00:00','1998-12-01 00:00:00',NULL,NULL,NULL,'Enrolled'),
(1950,55405,'2015-12-15 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled'),
(1950,55406,'2015-12-15 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled'),
(1950,55407,'2016-01-12 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled'),
(1950,55408,'2016-01-12 00:00:00','1998-11-02 00:00:00','2011-01-19 00:00:00',NULL,'2011-08-31 00:00:00',NULL,'Not Enrolled'),
(1950,55409,'2016-01-12 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled'),
(1950,55410,'2016-01-12 00:00:00','1998-11-02 00:00:00',NULL,NULL,NULL,NULL,'Not Enrolled')


select e.* 
from #emp e
inner join
(select EmpID, Max(EffectiveDate) dt
from #emp
--Attempted multiple filtering methods here while trying to avoid a sub-select
group by EmpID) e2 on e2.EmpID = e.EmpID and e2.dt = e.EffectiveDate

Answer 1

我会使用窗口函数来做到这一点，但那是另一回事。如果我没理解错的话，你想要这样的逻辑:

select e.* 
from #emp e inner join
     (select EmpID,
             coalesce(max(case when EnrollmentStatus = 'Enrolled' then EffectiveDate end), 
                      max(EffectiveDate)
                     ) dt
      from #emp
--Attempted multiple filtering methods here while trying to avoid a sub-select
      group by EmpID
     ) e2
     on e2.EmpID = e.EmpID and e2.dt = e.EffectiveDate;

这会获取 'Enrolled' 的日期(如果存在)。否则，它将获得最大日期。