SQL 按月和年分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5406170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL grouping by month and year
提问by wegelagerer
I'm not sure what should I write in the following SQL query to show 'date' column like this: "month-year" - "9-2011".
我不确定我应该在下面的 SQL 查询中写什么来显示像这样的“日期”列：“月-年”-“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.
所以，我想要做的是将第一列中的数据更改为显示月份和年份，而不是仅显示月份。
回答by Lamak
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))
Or as @40-Love mentioned you can cast with leading zeroes:
或者正如@40-Love 提到的，您可以使用前导零进行转换：
GROUP BY
CAST(YEAR(date) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(date) AS VARCHAR(2)), 2)
回答by Dumitrescu Bogdan
I guess is MS SQL as it looks like MS SQL syntax.
我猜是 MS SQL，因为它看起来像 MS SQL 语法。
So you should put in the group line the same thing as in select ex:
所以你应该在 group 行中放入与 select ex 中相同的内容：
Select MONTH(date)+'-'+YEAR(date), ....
...
...
...
group by MONTH(date)+'-'+YEAR(date)
回答by eupton
If I understand correctly. In order to group your results as requested, your Group By clause needs to have the same expression as your select statement.
如果我理解正确的话。为了按要求对您的结果进行分组，您的 Group By 子句需要与您的 select 语句具有相同的表达式。
GROUP BY MONTH(date) + '.' + YEAR(date)
To display the date as "month-date" format change the '.' to '-' The full syntax would be something like this.
要将日期显示为“月-日”格式，请更改“.” '-' 完整的语法是这样的。
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)
回答by YHTAN
SQL Server 2012 above, I prefer use format() function, more simplify.
SQL Server 2012 以上，我更喜欢使用 format() 函数，更简化。
SELECT format(date,'MM.yyyy') 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 format(date,'MM.yyyy')
回答by user unknown
In postgresql I can write a similar query with a date-format function (to_char) and grouping just by date:
在 postgresql 中，我可以使用日期格式函数 (to_char) 编写一个类似的查询，并仅按日期分组：
SELECT to_char (datum, 'MM-YYYY') AS mjesec
FROM test
GROUP BY datum
ORDER BY datum;
Such thing is surely possible with SQL-Server too, isn't it?
SQL-Server 肯定也有这种可能，不是吗？
回答by TaKeYakumo
For mariaDB you can:
对于 mariaDB，您可以：
SELECT DATE_FORMAT(date, '%m-%Y')
FROM [Order]
GROUP BY
DATE_FORMAT(date, '%m-%Y')
回答by MelOS
Yet another alternative:
Select FORMAT(date,'MM.yy')
...
...
group by FORMAT(date,'MM.yy')
回答by Erik Mattson
You can try multiplication to adjust the year and month so they will be one number. This, from my tests, runs much faster than format(date,'yyyy.MM'). I prefer having the year before month for sorting purpose. Code created from MS SQL Server Express Version 12.0.
您可以尝试乘法来调整年和月，使它们成为一个数字。从我的测试来看，这比format(date,'yyyy.MM'). 我更喜欢在前一个月进行排序。从 MS SQL Server Express 版本 12.0 创建的代码。
SELECT (YEAR(Date) * 100) + MONTH(Date) AS yyyyMM
FROM [Order]
...
GROUP BY (YEAR(Date) * 100) + MONTH(Date)
ORDER BY yyyyMM
回答by IUmpierrez
If you want to stay having the field in datetime datatype, try using this:
如果您想保留日期时间数据类型的字段，请尝试使用以下命令：
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, o.[date]), 0) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada
FROM [Order] o
WHERE (idCustomer = 1) AND (o.[date] BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, o.[date]), 0)
It it also easy to change to group by hours, days, weeks, years...
I hope it is of use to someone,
按小时、天、周、年更改分组也很容易......
我希望它对某人有用，
Regards!
问候！
