1、按周查询:
select DATE_FORMAT(user_food_date,'%X-%V') n from ho_user_sport
b where b.user_id=2 group by n
2、按月查询:
select distinct
DATE_FORMAT(user_food_date,'%X-%c') m from ho_user_food a where
a.user_id=2 union select DATE_FORMAT(user_sport_date,'%X-%c') n
from ho_user_sport b where b.user_id=2 order by m asc
3、按周统计,并且周在某个范围内:
select
DATE_FORMAT(user_food_date,'%X-%V') as m,
sum(TRUNCATE(user_food_share*food_basic_calorie,2)) as intaketotal from
ho_user_food ,ho_food_tw where
ho_food_tw
.UUID_FOOD_ID=ho_user_food.UUID_FOOD_ID and user_id=2 and
user_food_date in (select distinct user_food_date from ho_user_food a
where
a.user_id=2 union select
user_sport_date from ho_user_sport b where b.user_id=2 order by
user_food_date asc ) group by m limit 0,6
4、按周统计,并且查询出小于xx年第X周的所有数据:(小于2009年第10周的数据)
select count(*) from (
select distinct DATE_FORMAT(user_food_date,'%X-%V') m from ho_user_food a where a.user_id=2 group by m union
select DATE_FORMAT(user_sport_date,'%X-%V') n from ho_user_sport b where b.user_id=2 group by n
) as t where t.m