sql - 按月排序数据，然后按年排序

Question

                        Table Folder

      Column       |           Type           |                              Modifiers                          
-------------------+--------------------------+---------------------------------
 ID                | integer                  | not null default 
 Name              | character varying        | not null
 Size              | bigint                   | not null
 Timestamp         | timestamp with time zone | 

尝试获取 2014 年上传的所有文件的计数。以及同年的每月计数。

SELECT COUNT(*) FROM  "File" WHERE "Timestamp" >  '2014-01-01 21:53:23+08'

Answer 1

SELECT TO_CHAR(Timestamp, 'Mon') AS month,
       COUNT(*) AS fileCount
FROM File
WHERE EXTRACT(YEAR FROM Timestamp) = 2014
GROUP BY TO_CHAR(Timestamp, 'Mon')

如果您想要一份报告显示多年的月度分割，那么您可以稍微修改上面的查询:

SELECT TO_CHAR(Timestamp, 'Mon') AS month,
       EXTRACT(YEAR FROM Timestamp) AS year,
       COUNT(*) AS fileCount
FROM File
WHERE EXTRACT(YEAR FROM Timestamp) IN (2014, 2015, ...)
GROUP BY TO_CHAR(Timestamp, 'Mon'),
         EXTRACT(YEAR FROM Timestamp)