mysql - 在 MySQL SELECT 语句中插入空白行

Question

我有一个这样的查询:

SELECT COUNT(id), MONTH(date), YEAR(date)
FROM activity
GROUP BY YEAR(date) DESC, MONTH(date) DESC
ORDER BY YEAR(date) DESC, MONTH(date) DESC

按月/日期对记录进行排序和分组。如果某个月份没有记录，有什么办法可以插入空白行吗？

所以，而不是这个返回:

c | M% |  Y%

4 | 01 | 2014     # 4 records for Jan 2014
3 | 11 | 2013     # 3 records for Nov 2013
7 | 10 | 2013     # 7 records for Oct 2013

我想插入找不到记录的月份(2013 年 1 月，计数 = 0)，这样我就可以对每月事件进行清晰的可视化。

c | M% |  Y%

4 | 01 | 2014
0 | 12 | 2013    # <<<< no records for Dec 2013, but I still want it in array
3 | 11 | 2013
7 | 10 | 2013

Answer 1

与其他方法相反，您可以利用 SQL 变量在 MySQL 查询中进行编辑。我的内部查询创建了一个比 Now() 早一个月的基准日期。将其连接到事件表只是为了获取要使用的行。只需将 SQL 变量设置为比前一个月结果少一个月即可创建该列。在此示例中，我将限制设置为 6，因此它仅返回 6 个月的数据，但您可以将其更改为您关心的任意数量...只要“事件”表中有那么多记录即可(可以是任何表，只要它具有与您想要创建这些占位符记录一样多的记录)。这将创建一个结果集我有“DynamicCalendar”。然后，我以此为基础对按月/年连接的事件进行左连接

SELECT
      MONTH( DynamicCalendar.GrpDate ) as Mth,
      YEAR(  DynamicCalendar.GrpDate ) as Yr,
      COUNT(activity.id) as Entries
   from 
      ( select
              @BaseDate := date(date_sub(@BaseDate, interval 1 month)) as GrpDate
           from 
              ( select @BaseDate := date_add(Now(), interval 1 month)) sqlvars,
              Activity,
           limit
             6 ) DynamicCalendar
      LEFT JOIN Activity
         ON MONTH( DynamicCalendar.GrpDate ) = MONTH( Activity.Date )
         AND YEAR( DynamicCalendar.GrpDate ) = YEAR( Activity.Date )
   group by
      MONTH( DynamicCalendar.GrpDate ) as Mth,
      YEAR(  DynamicCalendar.GrpDate ) as Yr
   order by
      YEAR(  DynamicCalendar.GrpDate ) DESC,
      MONTH( DynamicCalendar.GrpDate )