mysql按周分组

Question

我有一张有以下字段的表格：

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

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

示例：

week 1 = $26.00  
week 2 = $35.00  
week 3 = $49.00

等等，我正在使用这个查询，但它不起作用：

  SELECT SUM(`amount_sale`) as total 
    FROM `sales` 
   WHERE `payment_type` = 'Account' 
GROUP BY WEEK(`the_date`)

Answer 1

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

 SELECT SUM(`amount_sale`) as total 
FROM `sales` 
WHERE `payment_type` = 'Account' 
GROUP BY WEEK(FROM_UNIXTIME(`the_date`))  

更新

另外，您可能需要输出周数，

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

Answer 2

还可以选择查询中的周数，如下所示：

  SELECT SUM(`amount_sale`) as total, WEEK(`the_date`) as week
  FROM `sales` 
  WHERE `payment_type` = 'Account' 
  GROUP BY week ORDER BY week ASC

如果您有数年的时间，您也可以从the_date中选择年份，并在此基础上订购，例如

  ORDER BY week ASC, year ASC

Answer 3

带上你的date和get the week

SELECT DATEPART( wk, getdate())  --this is assuming SQL server

然后group在那上面。