使用 SQL 查询返回每周创建的票证数量

Question

我有一个票证表 common_ticket，其中有一列名为creation_date，用于保存创建日期。

我想计算过去几个月每周创建的票证数量。我在编写 SQL 查询来返回此类信息时遇到问题。如何返回并不重要，只要每个单独的周都有一个不同的数字即可。

有人对如何做到这一点有任何想法吗？

Answer 1

像这样的东西：

SELECT extract(week from creation_date), 
       extract(year from creation_date),
       count(*)
FROM tickets
GROUP BY extract(week from creation_date), 
         extract(year from creation_date)

Answer 2

这应该可以做到：

SELECT [t1].[value] AS [Year], [t1].[value2] AS [Week], COUNT(*) AS [Count]
FROM (
    SELECT DATEPART(Year, [t0].[creation_date]) AS [value],
        DATEPART(Week, [t0].[creation_date]) AS [value2]
    FROM [common_ticket] AS [t0]
) AS [t1]
GROUP BY [t1].[value], [t1].[value2];