MySql 返回特定周内的日期范围

Question

我可以使用下面的代码检索特定一周内所有商品的总销售额。

select week(real_pur_date) as week, itemcode, sum(quantity)
from sales
where week(real_pur_date) = week(20120620)
group by itemcode;

输出为:

+------+----------+---------------+
| week | itemcode | sum(quantity) |
+------+----------+---------------+
|   25 | KB001    |            11 |
|   25 | KB002    |             2 |
|   25 | KB003    |             3 |
+------+----------+---------------+

这就是我想要的

问题是如何查询 MySql 将第 25 周的日期范围返回到结果表中？
例如，
1.第24周的日期范围是2012年6月10日至2012年6月16日。
2. 第25周的日期范围是2012年6月17日至2012年6月23日。

提前致谢。

Answer 1

我不是 MySQL 专家，所以我不得不尝试从 SQL Server 函数进行转换。但是，基本的数学知识就在那里。我将让您决定如何传递或计算@YEARSTART 和@YEAREND。

DECLARE @YEARSTART datetime, @YEAREND datatime
SET @YEARSTART='1/1/2012'
SET @YEAREND = '12/31/2012'

SELECT A.week, CASE WHEN (@YEARSTART + INTERVAL (8-DAYOFWEEK(@STARTYEAR)+(week-2)*7) DAY) < @YEARSTART 
                 THEN @YEARSTART
                 ELSE (@YEARSTART + INTERVAL (8-DAYOFWEEK(@STARTYEAR)+(week-2)*7) DAY) 
               END AS WeekStart,
               CASE WHEN (@YEARSTART + INTERVAL (7-DAYOFWEEK(@STARTYEAR)+(week-1)*7) DAY) > @YEAREND
                 THEN @YEAREND
                 ELSE (@YEARSTART + INTERVAL (7-DAYOFWEEK(@STARTYEAR)+(week-1)*7) DAY)
               END AS WeekEnd,
      A.itemcode, A.SumQty 
FROM
(
select week(real_pur_date) as week, itemcode, sum(quantity) AS SumQty
from sales
where week(real_pur_date) = week(20120620)
group by itemcode
) AS A