组日期双周(Group Date Bi

组日期双周(Group Date Bi-Weekly)

我试图在我的平板电脑组中每两周总结subtotals 。 关于如何过滤最后两周间隔有很多答案，但到目前为止还没有关于如何正确进行分组的有效解决方案。

表列： order_date ， subtotal

我每周都有成功的分组，但不知道如何每两周进行一次

SELECT CONCAT(YEAR(order_date), '/', WEEK(order_date)) AS week_name, SUM(subtotal) FROM m_orders

I am trying to sum subtotals in my tablet group bi-weekly. There are a lot of answers on how to filter last bi-weekly interval, but so far no working solution on how to properly do the grouping.

Table Columns: order_date, subtotal

I've had success with grouping weekly, but no idea how to do it biweekly

SELECT CONCAT(YEAR(order_date), '/', WEEK(order_date)) AS week_name, SUM(subtotal) FROM m_orders

最满意答案

group by WEEK(order_date) DIV 2将group by WEEK(order_date) DIV 2追加到您的查询中：

SELECT CONCAT(YEAR(order_date), '/', WEEK(order_date) DIV 2) AS fortnight_number, SUM(subtotal) FROM m_orders group by YEAR(order_date), WEEK(order_date) DIV 2

在年初和年末有一些问题，MySql的week函数的不同参数决定了返回的范围（0-53或1-53）以及如何确定一年中的第一周，但这是基本想法。

（根据业务需求，您可能需要week(order_date, 1) / 2第二个参数1指定周从星期一开始，范围从0到53，一年的第一周是第一周，有三个那一年或者更多的日子。当然，请与业务部门联系。）

编辑：更正/到DIV

Append group by WEEK(order_date) DIV 2 to your query:

SELECT CONCAT(YEAR(order_date), '/', WEEK(order_date) DIV 2) AS fortnight_number, SUM(subtotal) FROM m_orders group by YEAR(order_date), WEEK(order_date) DIV 2

There are some issues around start of year and end of year, and the different arguments to MySql's week function determine both the range returned (0-53 or 1-53) and how the first week of the year is determined, but this is the basic idea.

(Depending on business requirements, you probably want week(order_date, 1) / 2. The second argument, 1, specifies that weeks start on Mondays, range from 0-53, and the first week of the year is the first week with three or more days in that year. But check with the business, of course.)

Edit: corrected / to DIV