mysql - 优化MySQL查询(生成统计数据)

Question

数据库表:

我有 MySQL 查询:

select  calendar.datefield AS date, 
      ( SELECT  IFNULL(SUM(visits),0)
            FROM  link_stats
            WHERE  link_stats.link_id = '1'
              AND  statDate = date
      ) AS visits, 
      ( SELECT  Round(IFNULL(SUM(leads * (rate/1000)),0),3)
            FROM  link_stats
            WHERE  link_stats.link_id = '1'
              AND  statDate = date
      ) AS cash, 
      ( SELECT  IFNULL(SUM(leads),0)
            FROM  link_stats
            WHERE  link_stats.link_id = '1'
              AND  statDate = date
      ) AS leads
    from  `calendar`
    where  `calendar`.`datefield` between '2016-05-10' AND '2016-05-11' 

我尝试为每个包含空白天的链接生成统计报告。

示例:

在2016年1月11日至2016年1月20日期间，链接没有日期为2016年1月12日的记录。

Answer 1

建立一个包含所有可能日期的表格。将其命名为 TableOfDates，并有一列:日期字段

然后重新排列查询，如下所示:

select  d.datefield AS date,
        ls.visits, ls.cash, ls.leads
    from  TableOfDates d
    LEFT JOIN  `calendar` AS c  ON c.datefield = d.datefield
    LEFT JOIN  
      ( SELECT  statDate,
                IFNULL(SUM(visits),0) AS visits,
                Round(IFNULL(SUM(leads * (rate/1000)), 0),3) AS cash,
                IFNULL(SUM(leads),0) AS leads
            FROM  link_stats
            WHERE    ls.link_id = 1
              AND    statDate between '2016-01-11' AND '2016-01-20'
            GROUP BY statDate 
      ) AS ls  ON ls.statDate = d.datefield
    where  d.datefield        between '2016-01-11' AND '2016-01-20'