基于时间间隔选择行

Question

莱夫茨说我有一张桌子-> comments

id| comment | thread_id |         time       |  
1    xyz        1         2013-1-10 19:21:17
2    xyz        1         2013-1-11 19:21:17
3    xyz        2         2013-1-14 19:21:17
4    xyz        2         2013-2-10 19:21:17
5    xyz        1         2013-2-10 19:21:17
6    xyz        1         2013-2-10 19:21:17
7    xyz        1         2013-2-10 19:21:17
8    xyz        1         2013-4-10 19:21:17
9    xyz        1         2013-4-10 19:21:17
10   xyz        1         2013-6-10 19:21:17

现在，我希望在每个月的COUNT()间隔的特定thread_id中得到注释的总No

所以我有一个类似于->的数组(如果我们取thread_id = 1)

$total[0] => 2 
$total[1] => 6
$total[2] => 6 
$total[3] => 8
$total[4] => 8
$total[5] => 9

...so on on $total11 => 9

我每个月可以通过12次查询来完成这一任务，但这并不是很好。

任何人都可以用一个查询来完成它吗？

Answer 1

要生成完整的月份，您可以使用以下设备，基本上是列出所有可能的值，然后通过左联接将数据附加到此：

-- just an example
SELECT
      *
FROM (
      select 1  as th union all
      select 2  as th union all
      select 3  as th union all
      select 4  as th union all
      select 5  as th union all
      select 6  as th union all
      select 7  as th union all
      select 8  as th union all
      select 9  as th union all
      select 10 as th union all
      select 11 as th union all
      select 12
      ) mon
      LEFT JOIN (
                  SELECT
                        MONTH(time)      AS Month
                      , COUNT(thread_id) AS No_of_Comment
                  FROM comments
                  GROUP BY
                        MONTH(time)
            ) dat
                  ON mon.th = dat.month

Answer 2

是的，您可以通过使用group by语句来做到这一点：

以下是用于此的Sql：-

Select month(time) as Month, COUNT(thread_id) as No_of_Comment from comments group by month(time)