mysql 周平均值_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