mysql - 避免 NULL 计数 mysql

Question

我有这个选择:

select 'fv' prefix, c.user, MAX(f.data) as data, c.user, count(c.user) as logs
from favorites f inner join
     cadastro c
     ON f.user = c.id
where f.fv = '1' and c.user <> f.user and f.data > now()

如果我没有结果可显示，mysql 将显示以下内容:

prefix - user - data - user - logs
fv NULL NULL NULL 0

没有结果时我不想显示。怎么了？是计数(c.user)吗？如何避免计数到 0？

Answer 1

没有group by的聚合查询总是返回一行。一种简单的方法是添加聚合:

select 'fv' as prefix, c.user, MAX(f.data) as data, c.user, count(c.user) as logs
from favorites f inner join
     cadastro c
     ON f.user = c.id
where f.fv = '1' and c.user <> f.user and f.data > now()
group by f.fv;

如果没有匹配项，则不会返回任何行。