mysql常用语法

select convert(t2.name using gb2312), count(*) cnt from tbl_task_info t1 left JOIN tbl_type t2 on t1.type=t2.id where t1.owner in(312039) group by t1.type order by cnt desc


// 按类型统计
select tmp1.id, tmp1.name, ifnull(tmp2.cnt,0) 
from tbl_type tmp1
left join 
(select type, count(*) cnt 
from tbl_task_info 
where owner in(1111) 
and state <> 4 
group by type) tmp2
on tmp1.id=tmp2.type
order by cnt desc


// 按根因统计
select tmp1.id, tmp1.name, ifnull(tmp2.cnt,0) 
from tbl_reason tmp1
left join 
(select reason, count(*) cnt 
from tbl_task_info 
where owner in(1111) 
and state <> 4 
group by reason) tmp2
on tmp1.id=tmp2.reason
order by cnt desc


// 按时间统计
// %Y%u  周
// %Y%m%d  日
// %Y%m   月
select DATE_FORMAT(createtime,'%Y%u') gp, count(*) cnt 
from tbl_task_info 
where owner in(1111) 
and state <> 4 
group by gp

// 某个时间段闭区间
select count(*) cnt 
from tbl_task_info 
where owner in(1111) 
and state <> 4
and createtime >= '20160601' and createtime <= '20160606 24:00:00'