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