mysql - 用case在mysql中进行过滤

我有个问题我有3列id，paydate，paydatefin

SELECT id, paydate, CASE
WHEN paydatefin IS NULL
THEN paydatefin = paydate
ELSE paydatefin
END AS  `paydatefin`
FROM  `ws_crm_comenzi`
WHERE  `sezonId` =  '4'
AND  `service_id` =  '1'
AND  `owner_id` =  '325'
AND  `finished` =  '1'
ORDER BY  `paydatefin` DESC



---------------------------------
|id | paydate    | paydatefin   |
|1  | 2016-08-29 | 2016-08-30   |
|2  | 2016-08-28 | 0000-00-00   |
|3  | 2016-08-28 | 2016-08-29   |

如何查询paydatefin是否为空，然后paydatefin=paydate

最佳答案

当paydatefin是NULL时，您需要提供paydate来代替它。
但是你在做一个比较，例如(paydatefin = paydate)会导致0/1。
请改为：

SELECT
    id,
    paydate,
    CASE
    WHEN paydatefin IS NULL THEN paydate
    ELSE    paydatefin END AS `paydatefinColumn`
FROM
    `ws_crm_comenzi`
WHERE
    `sezonId` = '4'
AND `service_id` = '1'
AND `owner_id` = '325'
AND `finished` = '1'
ORDER BY `paydatefinColumn` DESC

编辑：

SELECT
        id,
        paydate,
        CASE
        WHEN paydatefin IS NULL OR paydatefin = '0000-00-00' THEN paydate
        ELSE    paydatefin END AS `paydatefinColumn`
    FROM
        `ws_crm_comenzi`
    WHERE
        `sezonId` = '4'
    AND `service_id` = '1'
    AND `owner_id` = '325'
    AND `finished` = '1'
    ORDER BY `paydatefinColumn` DESC