MySQL - 如何消除具有 NULL 值的结果？ (带代码)

Question

这是我的SQL代码

SELECT p.plant_name, 
       sum(data.value_1) totalvalue_1
  FROM (SELECT DISTINCT date_format(date, '%Y-%m') ym 
          FROM data 
         WHERE data.id_fk = 1 
           AND date BETWEEN '2011-01-01' AND '2011-04-01') dates
CROSS JOIN (SELECT DISTINCT data.id_fk, 
                   data.plant_id_fk 
              FROM data 
             WHERE data.id_fk = 1) up
      JOIN plants p ON p.plant_id = up.plant_id_fk
 LEFT JOIN data ON date_format(data.date, '%Y-%m') = dates.ym
               AND up.id_fk = data.id_fk
               AND up.plant_id_fk = data.plant_id_fk
               AND category_1 = 'expenses'
  GROUP BY up.plant_id_fk
  ORDER BY up.id_fk, up.plant_id_fk

输出这个结果

aasd    74
qweqwe  20
tyutyu  NULL
bnmbnm  NULL
234234  NULL

我想完全消除包含 NULL 的结果，只得到这个作为我的结果:

aasd      74
qweqwe  20

我试过在几个子句中使用 is not null 但没有一个给出我需要的结果。

有什么建议吗？

Answer 1

尝试在 GROUP BY 和 ORDER BY 之间添加一个 HAVING 子句:

 group by up.plant_id_fk
 HAVING SUM(data.value_1) > 0