SQL 按月和年分组 [英] SQL grouping by month and year

问题描述

我不知道我应该在下面的 SQL 查询中写什么来显示像这样的日期"列:月-年"-9-2011".

I'm not sure what should I write in the following SQL query to show 'date' column like this: "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)

所以，我想要做的是将第一列中的数据更改为显示月份和年份，而不是仅显示月份.

So, what I want to do is to change the data from the first column to show month and year instead of showing month only.

推荐答案

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 提到的，您可以使用前导零进行转换:

Or as @40-Love mentioned you can cast with leading zeroes:

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

这篇关于SQL 按月和年分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！