php 当只有日期时间字段时，如何按月份和年份分组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1559201/
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
How do I group by month and year when only having a datetime field?
提问by Extrakun
I have a table schema which is essentially a bunch of transaction info with a datetime field
我有一个表模式，它本质上是一堆带有日期时间字段的事务信息
TRANSACTION (< transactionid >, amount, when)
I need to generate a monthly total of transactions, which is SUM(amount), but I stumped by what do I group by. Each row from SQL should contain the monthly total (so one row for Jan 09, Feb 09....Jan 2010 and so on). I am thinking that I may have to generate the table by code, but would like to learn if there is a way to resolve this using SQL.
我需要生成每月的总交易量，即 SUM(amount)，但我对分组依据的内容感到困惑。SQL 中的每一行都应包含每月的总数（因此，1 月 9 日、2 月 9 日....2010 年 1 月等为一行）。我想我可能必须通过代码生成表，但想了解是否有办法使用 SQL 解决此问题。
Any help would be appreciated! (Using MySQL 5.3, PHP5)
任何帮助，将不胜感激！（使用 MySQL 5.3、PHP5）
回答by prostynick
You need to group by extracts.
您需要按提取物分组。
SELECT
SUM(amount)
FROM
transaction
GROUP BY
EXTRACT(MONTH FROM when),
EXTRACT(YEAR FROM when)
And if you need those columns, then
如果你需要这些列，那么
SELECT
EXTRACT(MONTH FROM when) as month,
EXTRACT(YEAR FROM when) as year,
SUM(amount)
FROM
transaction
GROUP BY
month,
year
Of course you can append ORDER BYand use short names too:
当然，您也可以附加ORDER BY和使用短名称：
SELECT
EXTRACT(MONTH FROM when) as month,
EXTRACT(YEAR FROM when) as year,
SUM(amount)
FROM
transaction
GROUP BY
month,
year
ORDER BY
year DESC,
month DESC
回答by manji
SELECT EXTRACT(YEAR_MONTH FROM when), sum(amount)
FROM TRANSACTION
GROUP BY EXTRACT(YEAR_MONTH FROM when)
回答by Bobby
I've always used MONTH() and YEAR()...which seems a little bit like a Hack to me, but it works...
我一直使用 MONTH() 和 YEAR() ......对我来说这似乎有点像黑客，但它有效......
SELECT SUM(amount) FROM yourTable GROUP BY MONTH(date), YEAR(date)
Or was it the other way round? thinks
或者是相反的？认为
Bobby
鲍比
回答by Grzegorz Gierlik
I would try something like that:
我会尝试这样的事情：
SELECT
YEAR(when) AS year,
MONTH(when) AS month,
SUM(amount) AS amount
FROM
TRANSACTION
GROUP BY
YEAR(when),
MONTH(when)
ORDER BY
YEAR(when),
MONTH(when)
This works on MS SQL and should work on MySQL too.
这适用于 MS SQL，也适用于 MySQL。
回答by troelskn
I think you want:
我想你想要：
SELECT SUM(amount)
FROM yourTable
GROUP BY CONCAT(YEAR(date), '-', MONTH(date))
