mysql按周分组 | mysql按周分组

本文介绍了mysql按周分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个包含以下字段的表:

I have a table with the following fields:

id
amount_sale
the_date(unix时间戳整数)
payment_type(可以是现金，也可以是帐户)

我正在尝试创建一个查询，该查询将按一年中的每个星期对所有销售额进行分组，然后在我的页面上划分每周的amount_sales总和.

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.

示例:

week 1 = $26.00
week 2 = $35.00
week 3 = $49.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`)

推荐答案

如果将the_date存储为整数，则首先需要使用FROM_UNIXTIME函数将其转换为日期时间:

If you store the_date as integer, you first need to convert it to datetime using FROM_UNIXTIME function:

 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`))

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