根据max(date)或NULL date选择不同的记录

Question

我试图根据员工状态或最近的终止日期获得员工列表。如果雇员是在职的，终止日期将为NULL。还有一些员工在我们组织内的多个公司工作过，我只想要最近的公司的记录，无论是活跃的还是已终止的。一个雇员在不同的公司也可能有不同的雇员号码，因此选择必须基于SSN (Fica)号码。

这里是原始数据集:

company employee    Fica    First_name  emp_status  Term_date
5       7026                Jason       T1          2013-09-16 00:00:00.000
500     7026                Jason       T1          2010-11-30 00:00:00.000
7       7026                Jason       T1          2009-07-31 00:00:00.000
2       90908               Jason       A1          NULL
505     293866              William     T1          2008-05-23 00:00:00.000
7       7243                Ashley      T1          2010-07-11 00:00:00.000
2       90478               Michael     T1          2013-01-11 00:00:00.000
500     90478               Michael     T1          2011-09-26 00:00:00.000
500     311002              Andreas     A1          NULL
3       365463              Matthew     A1          NULL
500     248766              Chris       T1          2007-04-23 00:00:00.000
500     90692               Kaitlyn     T1          2012-03-13 00:00:00.000
2       90692               Kaitlyn     A5          NULL
500     90236               Jeff        T1          2011-09-26 00:00:00.000
2       90236               Jeff        A1          NULL
2       90433               Nathan      T1          2012-03-26 00:00:00.000
500     90433               Nathan      T1          2011-09-26 00:00:00.000

这是我想要得到的结果:

company employee    Fica    First_name  emp_status  Term_date
2       90908               Jason       A1          NULL
505     293866              William     T1          2008-05-23 00:00:00.000
7       7243                Ashley      T1          2010-07-11 00:00:00.000
2       90478               Michael     T1          2013-01-11 00:00:00.000
500     311002              Andreas     A1          NULL
3       365463              Matthew     A1          NULL
500     248766              Chris       T1          2007-04-23 00:00:00.000
2       90692               Kaitlyn     A5          NULL
2       90236               Jeff        A1          NULL
2       90433               Nathan      T1          2012-03-26 00:00:00.000

谢谢你能给的任何帮助。我需要在SQL2005服务器上运行这个，它将通过ODBC连接到Oracle服务器。

Answer 1

如果日期都已填充，则可以使用"标准"not exists查询完成此操作。NULL引入了一个问题，但这个问题可以用coalesce()来解决:

select t.*
from table t
where not exists (select 1
                  from table t2
                  where t2.employee = t.employee and
                        coalesce(t2.term_date, '9999-01-01') > coalesce(t.term_date, '9999-01-01)
                 );

注意:如果你需要在Oracle上工作，那么你需要一个不同的日期常量格式。

编辑:

另一种解决此问题的方法使用row_number():

select t.*
from (select t.*,
             row_number() over (partition by employee
                                order by (case when term_date is null then 0 else 1 end),
                                         term_date desc
                               ) as seqnum
      from table t
     ) t
where seqnum = 1;

选择"最后"行的规则嵌入在order by子句中。将NULL的值放在首位，然后按降序排列term_date。