如何在mysql中从开始到结束日期获取每周明智的记录
问题描述：
我有一个名为 weight 的表，字段为 ID、WEIGHT、CREATED ON.我在 PHP 变量中也有 START 日期和 END 日期.现在我想获得从开始日期到结束日期的一周明智记录.
结果应该是这样的:
I have a Table called weight and fields are ID, WEIGHT, CREATED ON. I also have START date and END date in PHP variables. Now I want to get a week wise record from start date to end date.
Result should be like this:
WEEK | WEIGHT | CREATED ON
-----+--------+-----------
1 | 50 | 2012-02-01
1 | 50 | 2012-02-03
1 | 50 | 2012-02-05
1 | 50 | 2012-02-07
2 | 50 | 2012-02-08
2 | 50 | 2012-02-10
2 | 50 | 2012-02-14
3 | 50 | 2012-02-15
3 | 50 | 2012-02-17
3 | 50 | 2012-02-17
如何实现?
答
希望能帮到你
SELECT ID, weigth, created_on, WEEK(created_on) week
FROM weight
WHERE created_on BETWEEN start_date AND end_date
ORDER BY week