首頁 >資料庫 >mysql教程 >如何按月和年對 SQL 資料進行分組以進行報表?

如何按月和年對 SQL 資料進行分組以進行報表?

Mary-Kate Olsen
Mary-Kate Olsen原創
2025-01-05 12:10:43182瀏覽

How Can I Group SQL Data by Month and Year for Reporting?

按月和年將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中文網其他相關文章!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn