select DATE_FORMAT(create_time,'%Y') weeks,count(caseid) count from tc_case group by weeks;
select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;
select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;
-- 查询08月份的数据
select *
from epg_itvb_order_detail
where date_format(o_date,'%Y%m')='202108'
-- 多定义一个字段返回
-- 查询第34周的数据
select *
from epg_itvb_order_detail
where date_format(o_date,'%Y%u')='202134'
--周月主要就是 %m 和 %n的区别
-- 将数据库中符合要求的数据按照周的形式进行统计
select
id,user_id, content_name,content_code,singer,definition,content_category,
order_species,order_status,order_mode,setbox_id,ip,CP,
zone_code, zone_name,o_begintime,alter_date, version,
-- 一般都有求和什么的,周 的话在起个week别名,下面再通过group by 这个week
-- 在实体对象中创建一个week字段接收,string类型的
sum(order_count) as order_count, DATE_FORMAT(o_date,'%Y%u') week
from epg_itvb_order_detail
where
order_status = 0
group by content_code,order_species,week
-- 将数据库中符合要求的数据按照月的形式进行统计
SELECT
SUM(pd.play_count) as play_count,SUM(pd.play_duration) as play_duration,pd.CP,DATE_FORMAT(p_date,'%Y%m') month
FROM epg_itvb_play_detail pd
where 1 = 1
group by pd.CP,month