如何按月和年對 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中文網其他相關文章！