mysql 按周分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6710342/
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
mysql grouping by week
提问by scarhand
I have a table with the following fields:
我有一个包含以下字段的表：
- id
- amount_sale
- the_date (unix timestamp integer)
- payment_type (can be Cash, or Account)
- ID
- 销售量
- the_date（unix 时间戳整数）
- 付款类型（可以是现金或帐户）
I am trying to create a query that will group all sales by each week of the year, and then split the sum of amount_sales for each week on my page.
我正在尝试创建一个查询，该查询将按一年中的每一周对所有销售额进行分组，然后在我的页面上拆分每周的 amount_sales 总和。
Example:
例子：
week 1 = .00
week 2 = .00
week 3 = .00
etc. I'm using this query but it's not working:
等等。我正在使用这个查询，但它不起作用：
SELECT SUM(`amount_sale`) as total
FROM `sales`
WHERE `payment_type` = 'Account'
GROUP BY WEEK(`the_date`)
回答by a1ex07
If you store the_dateas integer, you first need to convert it to datetime using FROM_UNIXTIMEfunction:
如果存储the_date为整数，则首先需要使用FROM_UNIXTIME函数将其转换为日期时间：
SELECT SUM(`amount_sale`) as total
FROM `sales`
WHERE `payment_type` = 'Account'
GROUP BY WEEK(FROM_UNIXTIME(`the_date`))
UPDATE:
Also, you might want to output week number,
更新：
另外，您可能想输出周数，
SELECT CONCAT('Week ', WEEK(FROM_UNIXTIME(`the_date`))) as week_number,
SUM(`amount_sale`) as total
FROM `sales`
WHERE `payment_type` = 'Account'
GROUP BY WEEK(FROM_UNIXTIME(`the_date`))
回答by Erik
Try to also select the weeks in your query, like this:
尝试同时选择查询中的周，如下所示：
SELECT SUM(`amount_sale`) as total, WEEK(`the_date`) as week
FROM `sales`
WHERE `payment_type` = 'Account'
GROUP BY week ORDER BY week ASC
If you have weeks covering several years you could also select the year from the_dateand order on that as well, like
如果您有数周涵盖数年，您也可以从中选择年份the_date并订购，例如
ORDER BY week ASC, year ASC
回答by Limey
take your dateand get the week:
带上你的date和get the week：
SELECT DATEPART( wk, getdate()) --this is assuming SQL server
and then groupon that.
然后group在那。
回答by Chris Henry
Since the_dateis a unix timestamp, the WEEKfunction won't work. Instead, you can do this:
由于the_date是 unix 时间戳，该WEEK函数将不起作用。相反，您可以这样做：
GROUP BY week(FROM_UNIXTIME(the_date))
按周分组(FROM_UNIXTIME(the_date))
Keep in mind, this will not perform well, as that function will need to be applied to every row in the table you're querying.
请记住，这不会很好地执行，因为该函数需要应用于您正在查询的表中的每一行。
