温馨提示:本文翻译自stackoverflow.com,查看原文请点击:sql server - SQL grouping by month and year
sql sql-server

sql server - SQL按月和年分组

发布于 2020-03-27 10:22:57

我不确定在以下SQL查询中应该写什么来显示“日期”列,例如:“ month-year”-“ 9-2011”。

SELECT MONTH(date) + '.' + YEAR(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)

因此,我要做的是将第一列中的数据更改为显示月份和年份,而不是仅显示月份。

查看更多

查看更多

提问者
wegelagerer
被浏览
230
25.1k 2015-12-14 18:05
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))

或者如@ 40-Love所述,您可以使用前导零进行转换:

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