mysql - 用 case 在 mysql 中创建一个过滤器

Question

我有一个问题，我有 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 为 null 那么 paydatefin = paydate 我该如何查询

Answer 1

当 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