日期返回null而不是空值的MySQL

问题描述：

所以我有一个临时表，我需要有应对扩招的历史记录来填充日期返回null而不是空值的MySQL

我有一个数据集，看起来像这样的工作：

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和not null值。

它必须满足的条件是

Disenroll日期不是空
Disenroll日期为空，
记录有多个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;

然而，这返回的记录太多了，有没有办法隔离这个查询，也许更简单，这可以防止它通过约束或参数返回太多的记录？

感谢所有

条件“Disenroll日期*不为空*”和“Disenroll日期*为空*”似乎并没有有效.. – 2012-03-09 20:32:47

我指的是disenroll日期是空的情况和该disenroll_date是非空有效日期...不是“不为空”。 – Hituptony 2012-03-14 19:31:30

答

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;

答

其实这照顾了...谢谢反正。

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

日期返回null而不是空值的MySQL

相关推荐