mysql按周分组

Question

查询：

select  repdate, week(repdate,1) from calldrivers 
where repdate between '2013-10-01' and '2014-01-12' group by repdate

结果：

.
.
.
    2013-12-28 52 

    2013-12-29 52 

    2013-12-30 53 

    2013-12-31 53 

    2014-01-01 1 

    2014-01-02 1 

    2014-01-03 1 

    2014-01-04 1 

    2014-01-05 1 

    2014-01-06 2 

    2014-01-07 2 
.
.
.

正如你所看到的，去年的最后一周没有被正确地分组。为了自动化的目的，我需要这是自动化的，这样mysql就可以将2013年12月30日到1月5日的5这一周识别为一周。

是否有解决办法来解决这个问题？

编辑**

我担心的是，当你使用组的每周(回复，1)，它将分组12月30日和31日作为一周和从1月1日至5日作为另一周。然而，他们应该是一个星期。

Answer 1

还有一个答案可以帮助你：

How to group by week in MySQL?

您可以以这种方式编写sql查询。

select  repdate, week(repdate,1) from calldrivers 
where repdate between '2013-10-01' and '2014-01-12' 
group by week(repdate,1)

这应该能行

编辑:测试用例

运行这个测试用例，对于哪种模式，您应该运行week函数，第一行代表测试用例日期的实际值。之后有几个查询和一个评论。注释是查询的结果。

-- SUNDAY, MONDAY, THURSDAY, WEDNESDAY, SUNDAY, MONDAY
SELECT WEEK('2013-12-29', 0), WEEK('2013-12-30', 0), WEEK('2013-12-31', 0), WEEK('2014-01-01', 0), WEEK('2014-01-05', 0), WEEK('2014-01-06', 0);
-- 52, 52, 52, 0, 1, 1
SELECT WEEK('2013-12-29', 1), WEEK('2013-12-30', 1), WEEK('2013-12-31', 1), WEEK('2014-01-01', 1), WEEK('2014-01-05', 1), WEEK('2014-01-06', 1);
-- 52, 53, 53, 1, 1, 2
SELECT WEEK('2013-12-29', 2), WEEK('2013-12-30', 2), WEEK('2013-12-31', 2), WEEK('2014-01-01', 2), WEEK('2014-01-05', 2), WEEK('2014-01-06', 2);
-- 52, 52, 52, 52, 1, 1
SELECT WEEK('2013-12-29', 3), WEEK('2013-12-30', 3), WEEK('2013-12-31', 3), WEEK('2014-01-01', 3), WEEK('2014-01-05', 3), WEEK('2014-01-06', 3);
-- 52, 1, 1, 1, 1, 2
SELECT WEEK('2013-12-29', 4), WEEK('2013-12-30', 4), WEEK('2013-12-31', 4), WEEK('2014-01-01', 4), WEEK('2014-01-05', 4), WEEK('2014-01-06', 4);
-- 53, 53, 53, 1, 2, 2
SELECT WEEK('2013-12-29', 5), WEEK('2013-12-30', 5), WEEK('2013-12-31', 5), WEEK('2014-01-01', 5), WEEK('2014-01-05', 5), WEEK('2014-01-06', 5);
-- 51, 52, 52, 0, 0, 1
SELECT WEEK('2013-12-29', 6), WEEK('2013-12-30', 6), WEEK('2013-12-31', 6), WEEK('2014-01-01', 6), WEEK('2014-01-05', 6), WEEK('2014-01-06', 6);
-- 1, 1, 1, 1, 2, 2
SELECT WEEK('2013-12-29', 7), WEEK('2013-12-30', 7), WEEK('2013-12-31', 7), WEEK('2014-01-01', 7), WEEK('2014-01-05', 7), WEEK('2014-01-06', 7);
-- 51, 52, 52, 52, 52, 1

我认为从周一到周日的模式是3，因为它在一年的第一周带来了12月30日和31日。如果你想要在一周内的第30和第31号12月和12月1日至5日在另一个星期，你必须选择模式5。