如何按月和年对 SQL 数据进行分组以进行报告？

按月和年对 SQL 数据进行分组

在 SQL 查询中，经常需要按月和年对数据进行分组进行分析。这涉及到创建一个表示所需分组组合的新列。

考虑以下查询：

SELECT MONTH(date) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada
FROM [Order]
WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY MONTH(date), YEAR(date)

此查询按月对数据进行分组，导致 9 月的输出类似于“9” 。要同时显示月份和年份，我们可以使用 CAST 函数将月份和年份值转换为字符串，并用连字符连接它们。

SELECT CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4)) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada
FROM [Order]
WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4))

此修改将以所需的格式显示结果，例如，“9-2011”。

或者，我们可以使用带有前导零的 CAST 函数来确保一致格式：

GROUP BY
  CAST(YEAR(date) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(date) AS VARCHAR(2)), 2)

这将导致输出如“2011-09”。

以上是如何按月和年对 SQL 数据进行分组以进行报告？的详细内容。更多信息请关注PHP中文网其他相关文章！