mysql - 如果在MySQL中列不为空，如何写条件

如果enrollDate和cancelDate相同，则需要消除行。所以我写了如下查询
 SELECT a.id, a.clientId, a.enrollDate, a.cancelDate FROM gap_borrower a WHERE STR_TO_DATE( a. loanEndDate , '%Y-%m-%d' ) >= STR_TO_DATE( '2015-12-31', '%Y-%m-%d' ) AND FIND_IN_SET( a.status, 'A,C' ) AND STR_TO_DATE( a. enrollDate , '%Y-%m-%d' ) <> STR_TO_DATE( a. cancelDate , '%Y-%m-%d' )

但是，如果cancelDate为Null，则它不会显示为不同。（仅需要消除上图中的id = 8551。这是我的问题）因此我使用if条件编写了另一个查询

SELECT a.id, a.clientId, a.enrollDate, a.cancelDate FROM gap_borrower a WHERE STR_TO_DATE( a.`loanEndDate` ,  '%Y-%m-%d' ) >= STR_TO_DATE(  '2015-12-31',  '%Y-%m-%d' ) AND FIND_IN_SET( a.status,  'A,C' ) AND (CASE WHEN a.cancelDate IS NOT NULL THEN (STR_TO_DATE( a.`enrollDate` ,  '%Y-%m-%d' ) <> STR_TO_DATE(  a.cancelDAte,  '%Y-%m-%d' )) END  )

它也失败。请指导我解决此问题

最佳答案

尝试这样的事情：

SELECT a.id, a.clientId, a.enrollDate, a.cancelDate
FROM gap_borrower a WHERE
    DATE(a.loanEndDate) >= DATE('2015-12-31')
    AND FIND_IN_SET( a.status,  'A,C' )
    AND (DATE(a.enrollDate) <> DATE(a.cancelDate) OR a.enrollDate IS NULL OR a.cancelDate IS NULL)
    --uncomment this if you don't need rows where  the both dates are null
    --AND (a.enrollDate IS NOT NULL or a.cancelDate IS NOT NULL)