mysql - 如何隐藏sql中为空的和值？

Question

如何删除包含空值的行？

<小时/>

我是 SQL 新手，所以如何隐藏包含空值的行？
我想隐藏或删除报表中具有空值的行，这样它只会显示包含一些数据的行。任何帮助将不胜感激...

SELECT `id`,
    SUM(CASE WHEN `start_date` BETWEEN '2015-01-01' AND '2015-01-25' AND `leave_type` = '1' AND `status`<>'Canceled' AND `s_status`='Approved' THEN `no_days` ELSE NULL END) AS 'annual',
    SUM(CASE WHEN `start_date` BETWEEN '2015-01-01' AND '2015-01-25' AND `leave_type` = '2' AND `status`<>'Canceled' AND `s_status`='Approved' THEN `no_days` ELSE NULL END)'sick',
    SUM(CASE WHEN `start_date` BETWEEN '2015-01-01' AND '2015-01-25' AND `leave_type` = '3' AND `status`<>'Canceled' AND `s_status`='Approved' THEN `no_days` ELSE NULL END)'compassionate',
    SUM(CASE WHEN `start_date` BETWEEN '2015-01-01' AND '2015-01-25' AND `leave_type` = '4' AND `status`<>'Canceled' AND `s_status`='Approved' THEN `no_days` ELSE NULL END)'maternity',
    SUM(CASE WHEN `start_date` BETWEEN '2015-01-01' AND '2015-01-25' AND `leave_type` = '5' AND `status`<>'Canceled' AND `s_status`='Approved' THEN `no_days` ELSE NULL END)'matrimonial',
    SUM(CASE WHEN `start_date` BETWEEN '2015-01-01' AND '2015-01-25' AND `leave_type` = '7' AND `status`<>'Canceled' AND `s_status`='Approved' THEN `no_days` ELSE NULL END)'unpaid'
 FROM `leave_history`
 GROUP BY `id`

<小时/>

这是我的查询的输出

id     annual    sick   compassionate  maternity  matrimonial  unpaid
2        20       40      (NULL)         (NULL)      (NULL)     (NULL)
5      (NULL)   (NULL)    (NULL)         (NULL)      (NULL)     (NULL)
11     (NULL)   (NULL)    (NULL)         (NULL)      (NULL)     (NULL)
55     (NULL)   (NULL)    (NULL)         (NULL)      (NULL)     (NULL)
135    1           1      (NULL)         (NULL)      (NULL)     (NULL)

Answer 1

检查 IS NULL/IS NOT NULL 谓词和 HAVING 子句。

SELECT id ,
        SUM(CASE WHEN start_date BETWEEN '2015-01-01' AND '2015-01-25' AND leave_type = '1' AND status<>'Canceled' AND s_status='Approved' THEN no_days ELSE NULL END) AS annual ,
        SUM(CASE WHEN start_date BETWEEN '2015-01-01' AND '2015-01-25' AND leave_type = '2' AND status<>'Canceled' AND s_status='Approved' THEN no_days ELSE NULL END) AS sick ,
        SUM(CASE WHEN start_date BETWEEN '2015-01-01' AND '2015-01-25' AND leave_type = '3' AND status<>'Canceled' AND s_status='Approved' THEN no_days ELSE NULL END) AS compassionate ,
        SUM(CASE WHEN start_date BETWEEN '2015-01-01' AND '2015-01-25' AND leave_type = '4' AND status<>'Canceled' AND s_status='Approved' THEN no_days ELSE NULL END) AS maternity ,
        SUM(CASE WHEN start_date BETWEEN '2015-01-01' AND '2015-01-25' AND leave_type = '5' AND status<>'Canceled' AND s_status='Approved' THEN no_days ELSE NULL END) AS matrimonial ,
        SUM(CASE WHEN start_date BETWEEN '2015-01-01' AND '2015-01-25' AND leave_type = '7' AND status<>'Canceled' AND s_status='Approved' THEN no_days ELSE NULL END) AS unpaid
FROM leave_history GROUP BY id
HAVING annual IS NOT NULL AND sick IS NOT NULL ... and so on