SQL语句: 按周、月统计总值 和 平均值

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<'2009-10'