如果使用group by的mysql查询中不存在数据,那么如何为sum、count设置默认值?

SELECT f.period as month,
       ifnull(sum(p.revenue * ((100-q.rate)/100)),0) as revenue,
       count(distinct q.label) as tot_stmt 
  FROM files f 
       LEFT JOIN reports p ON f.id = p.file_id
       LEFT JOIN rates q ON f.period = q.period
       LEFT JOIN albums a ON q.album_id = a.id AND p.upc = a.upc
 WHERE f.period in ('2010-06-01','2010-05-01','2010-04-01','2010-03-01') 
       AND f.period_closed = true
 GROUP BY month ORDER BY month DESC

说明:
重写要联接的条件
如果所有聚合记录在该列中的值均为空,则count(column)返回0
如果所有聚合记录在该列中的值均为空,则sum(column)返回空值
您还需要允许p.revenue为空(删除了该条件)

注:
你好像没有从相册表中得到任何东西,所以你可以把它拿出来

您需要一个包含所需信息的helper表。helper表将保存您所指的月份:

月

2010-06-01 2010-05-01 2010-04-01 2010-03-01

一旦你有了那张桌子,你就可以 左外连接 对它,使用 无空 函数来填充所需的默认值。

类似于:

SELECT f.period as month, 
       sum(p.revenue * ((100-q.rate)/100)) as revenue, 
       count(distinct q.label) as tot_stmt  
  FROM (files f left outer join periods pers on f.period = pers.period), reports p, rates q,albums a 
 WHERE f.period in ('2010-06-01','2010-05-01','2010-04-01','2010-03-01')  
   AND f.period_closed = true 
   AND q.period = f.period 
   AND a.id = q.album_id 
   AND p.file_id = f.id 
   AND p.upc = a.upc 
   AND p.revenue is not null 
 GROUP BY month ORDER BY month DESC