mysql如何查询某一天所在周的周一至周日的数据？

Question

本帖最后由 huahuagongzi9999 于 2014-11-04 16:09:05 编辑

Answer 1

好像这样可以SELECT WEEKDAY(ub.`createTime`) AS weekdays,ub.`userId`,ub.`axungeRate`,ub.`bmi`,ub.`bodyAge`,ub.`boneWeight`,ub.`calorieAsia`,ub.`calorieEurope`,ub.`muscleRate`,ub.`visceraAxunge`,ub.`waterRate`,ub.`weight`,ub.`createTime` FROM userBmi ub  
WHERE  UNIX_TIMESTAMP(ub.`createTime`)>(UNIX_TIMESTAMP('2014-10-26')-(6*86400)) AND UNIX_TIMESTAMP(ub.`createTime`)<(UNIX_TIMESTAMP('2014-10-26')+86400)  AND ub.`userId` =1000 ORDER BY createTime ASC;

Answer 2

SELECT WEEKDAY(ub.`createTime`) AS weekdays,ub.`userId`,ub.`axungeRate`,
ub.`bmi`,ub.`bodyAge`,ub.`boneWeight`,ub.`calorieAsia`,ub.`calorieEurope`,
ub.`muscleRate`,ub.`visceraAxunge`,ub.`waterRate`,ub.`weight`,ub.`createTime` 
FROM userBmi ub  
WHERE  ub.`createTime` between '2014-10-24'-INTERVAL WEEKDAY('2014-10-24') DAY and '2014-10-24'+ INTERVAL 6-WEEKDAY('2014-10-24') DAY
AND ub.`userId` =1000;

Answer 3

改为SELECT WEEKDAY(ub.`createTime`) AS weekdays,ub.`userId`,ub.`axungeRate`,
ub.`bmi`,ub.`bodyAge`,ub.`boneWeight`,ub.`calorieAsia`,ub.`calorieEurope`,
ub.`muscleRate`,ub.`visceraAxunge`,ub.`waterRate`,ub.`weight`,ub.`createTime` 
FROM userBmi ub  
WHERE  ub.`createTime` between '2014-10-24'-INTERVAL WEEKDAY('2014-10-24') DAY and '2014-10-24'+ INTERVAL 7-WEEKDAY('2014-10-24') DAY
AND ub.`userId` =1000;就可以了‘

Answer 4

获取日期所在周的周一
SELECT  DATE_SUB('2015/1/29',INTERVAL WEEKDAY('2015/1/29') DAY);
获取日期所在周的周日
SELECT  DATE_ADD('2015/1/29',INTERVAL WEEKDAY('2015/1/29') DAY);
获取日期所在周前一周的周日
SELECT DATE_SUB('2015/2/5', INTERVAL WEEKDAY('2015/2/5')+1 DAY);

Answer 5

从效率上说，用程序先算出日期在SQL查询效率更高。