mysql - mysql 日期返回空值和非空值

Question

所以我有一个临时表，我需要填充必须处理注册历史记录的记录

我使用的数据集如下所示:

sccphidsid    enroll_date disenroll_date    status
abc123x        2009-01-01  2010-31-12           0
abc123x        2011-01-01  null                 0
abc123x        2011-03-01  2012-01-01           0

所以我需要它返回具有相同 sccphidsid 的所有记录，并返回它们，包括 null 和非 null 值。

它必须满足的条件是

• 退出日期不为空
• 取消注册日期为空且
• 记录有多个 disenroll_dates

当前解决方案:

select *, count(disenroll_date is null)
              from enrollment _test
              where Status = 0
              group by Sub_Client_Cd, Policy_Holder_ID, Suffix_ID
              having count(disenroll_date is null) > 1;

但是，这返回了太多记录，有没有一种方法可以隔离此查询，也许更简单，可以防止通过约束或参数返回太多记录？

谢谢大家

Answer 1

SELECT Sub_Client_Cd, Policy_Holder_ID, Suffix_ID,
       SUM(CASE WHEN disenroll_date IS NULL THEN 1 ELSE 0 END) AS NullDateCount,
       SUM(CASE WHEN disenroll_date IS NOT NULL THEN 1 ELSE 0 END) AS NotNullDateCount
    FROM enrollment_test
    WHERE Status = 0
    GROUP BY Sub_Client_Cd, Policy_Holder_ID, Suffix_ID
    HAVING COUNT(*) > 1;