MySQL 按时间戳的月份部分分组的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18087659/
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 query to group by month part of timestamp
提问by Sam Creamer
I'm really bad at SQL queries but I'm learning so please forgive this question.
我真的很不擅长 SQL 查询，但我正在学习，所以请原谅这个问题。
Here is my current query:
这是我当前的查询：
SELECT TIMESTAMP, SUM( electricity ) AS electricity, `siteID`
FROM table
WHERE (
MONTH( `TimeStamp` ) =10)
GROUP BY siteID
My table looks like:
我的桌子看起来像：
#########################################
# Id # SiteID # TimeStamp # Elecricity #
# 0 # 100 # 10/08/2012 # 50 #
# 1 # 98 # 10/08/2012 # 32 #
# 2 # 100 # 10/09/2012 # 96 #
# 3 # 94 # 10/09/2012 # 25 #
# 4 # 100 # 10/10/2012 # 100 #
# 5 # 100 # 10/11/2012 # 55 #
#########################################
What I am trying to do is to sum up all of the days of each month of each site, so that I will have one answer per site and month. So in this example the query should return the sum of the electricity values for siteID 100 because they're all in month 10, the same for siteID 98 and 94.
我想要做的是总结每个站点每个月的所有天数，以便每个站点和每个月都有一个答案。因此，在此示例中，查询应返回 siteID 100 的电力值总和，因为它们都在第 10 个月，siteID 98 和 94 相同。
Thanks
谢谢
回答by Brian Hoover
SELECT month('TIMESTAMP'), SUM( electricity ) AS electricity, `siteID`
FROM table
WHERE (
MONTH( `TimeStamp` ) =10)
GROUP BY siteID, month('TIMESTAMP')
This will work. One thing that you have to think about is that month is not unique. Oct, 2012, in this case, is the same as Oct 2013. You might want to add another column for year.
这将起作用。您必须考虑的一件事是那个月不是唯一的。在这种情况下，2012 年 10 月与 2013 年 10 月相同。您可能需要为年份添加另一列。
