按年和月计数记录，包括零计数 [英] Counting records by year and month including zero counts

问题描述

我使用的是SQL Server Compact Edition服务器，我想计算每个月内对应于特定教程的日期范围内的评论数，包括计数为零的月份。我知道我需要加入一个日历表到我的表，以解决缺少的几个月，但我需要帮助正确实施这个。

I am using an SQL Server Compact Edition server and I want to count the number of comments per month that correspond to a certain tutorial within a range of dates and include months which have a count of zero. I know I need to join a "calendar" table to my table to account for the missing months, but I need help with correct implementation of this.

我有一个表来自不同教程的所有评论。这个表称为注释，我需要的列是 [Tutorial] （ nvarchar ）和 [DateAdded] （ DateTime ）。

I have a table of all the comments from different tutorials. This table is called Comments and the columns I need are [Tutorial] (nvarchar) and [DateAdded] (DateTime).

Tutorial |  DateAdded  
---------+-------------
sample   |  2013-09-02  
sample   |  2013-09-04
sample   |  2013-09-12
sample   |  2013-09-12
example  |  2013-09-15
sample   |  2013-09-16
sample   |  2013-09-21
sample   |  2013-09-30
sample   |  2013-10-01   
sample   |  2013-11-11
sample   |  2013-11-11
example  |  2013-11-14
sample   |  2013-11-15
sample   |  2013-11-19
sample   |  2013-11-21
sample   |  2013-11-25
sample   |  2014-02-04
sample   |  2014-02-06  

我有一个日历

And I have a Calendar table which has a year and month column like so:

Year | Month
-----+------
2000 | 01
2000 | 02
.    | .
.    | .
.    | .
2099 | 12

如果我正在寻找过去一年的样本评论的月度计数2014年2月14日），那么理想的输出将是：

If I were looking for the monthly count of the 'sample' comments from the past year (as of Feb. 14th, 2014), then the ideal output would be:

Tutorial | Year | Month | Count
---------+------+-------+------
 sample  | 2013 |  09   |  7
 sample  | 2013 |  10   |  1
 sample  | 2013 |  11   |  6
 sample  | 2013 |  12   |  0
 sample  | 2014 |  01   |  0
 sample  | 2014 |  02   |  2

我能够找出如何进行以下查询，但我需要几个月没有注释也返回0。

I was able to figure out how to do the following query, but I need the months that do not have comments to return 0 as well.

SELECT 
     Tutorial, 
     datepart(year, DateAdded) AS Year, 
     datepart(month, DateAdded) AS Month, 
     COUNT(*) AS Count From Comments 
WHERE 
     DateAdded > DATEADD(year,-1,GETDATE())  
       AND 
     Tutorial='sample' 
GROUP BY 
     Tutorial, 
     datepart(year, DateAdded), 
     datepart(month, DateAdded)

使用上面的示例数据输出。

Output using sample data from above.

Tutorial | Year | Month | Count
---------+------+-------+------
 sample  | 2013 |  09   |  7
 sample  | 2013 |  10   |  1
 sample  | 2013 |  11   |  6
 sample  | 2014 |  02   |  2

我知道我需要加入表，但是我似乎不知道使用或如何正确实现它。请记住，这是为SQL Server CE，所以不是所有的命令从SQL Server可以使用。

I know I need to join the tables, but I can't seem to figure out which join to use or how to implement it correctly. Please keep in mind that this is for SQL Server CE, so not all commands from SQL Server can be used.

非常感谢这么多！

推荐答案

已编辑（我忘记了教程列）

如果您有表月和年，您的应该尝试类似

If you have a Calendar table with Month and Year you should try something like

SELECT t2.Tutorial, t1.[Month], t1.[Year], COALESCE(t2.Number, 0) AS Result
  FROM Calendar AS t1 LEFT JOIN (
    SELECT 
      Tutorial, 
      CONVERT(NCHAR(6), DateAdded, 112) AS tutDate,
      COUNT(*) AS Count From Comments 
    WHERE 
      DateAdded > DATEADD(year,-1,GETDATE())  
      AND 
      Tutorial='sample' 
    GROUP BY 
      Tutorial, 
      CONVERT(NCHAR(6), [Order Date], 112)
  ) AS t2
  ON (t1.[Year] + t1.[Month]) = t2.tutDate
  ORDER BY t1.[Year] + t1.[Month]

这篇关于按年和月计数记录，包括零计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！