case when 和 SQL按按照天 星期 月 极度 半年 年 统计

// 1、按照天 星期 月 极度 半年 年 统计
select DATE_FORMAT(FROM_UNIXTIME(create_time, '%Y-%m-%d'),'%Y-%u') create_time1,count(id) as count FROM itest_tasks GROUP BY create_time1;
'%Y%m%d'
'%Y%u'//星期
'%Y%m'//月份
CONCAT(YEAR(FROM_UNIXTIME(create_time, '%Y-%m-%d')),'_',QUARTER(FROM_UNIXTIME(create_time, '%Y-%m-%d')),'Q') //季度
 CONCAT(CONVERT(YEAR(FROM_UNIXTIME(create_time, '%Y-%m-%d')), CHAR), IF(MONTH(FROM_UNIXTIME(create_time, '%Y-%m-%d'))<=6, CONVERT('上半年' USING utf8), CONVERT('下半年' USING utf8))) 半年
'%Y' //年度

//2、
//sum(case when itest_tasks.type_parent_id = 2 THEN 1 ELSE 0 END) num2
//count(case when itest_tasks.type_parent_id = 2 THEN itest_tasks.type_parent_id END) 意思一样
SELECT itest_tasks.project_id,sum(case when itest_tasks.type_parent_id = 1 THEN 1 ELSE 0 END) num1,sum(case when itest_tasks.type_parent_id = 2 THEN 1 ELSE 0 END) num2  from itest_tasks,itest_task_users WHERE itest_tasks.id = itest_task_users.task_id  and itest_tasks.project_id in (1000,1036,1039,1040,1041,1043,1044,1045,1046,1047) and complete_time >= -28800 and complete_time <= 1374076800  GROUP BY itest_tasks.project_id;

//3、
//case when 在某种情况下只统计某个字段
//case when type = 'official' THEN user_id END 表示在type = 'official'的情况下统计user_id字段
//case when 和 DISTINCT 一起使用
SELECT itest_tasks.project_id,SUM(real_hours) as sum_hour,COUNT(DISTINCT (user_id))  user_num,COUNT(DISTINCT (case when type = 'official' THEN user_id END))  user_num_official,COUNT(DISTINCT (case when type = 'waipin' THEN user_id END))  user_num_waipin,COUNT(DISTINCT (case when type = 'waibao' THEN user_id END))  user_num_waibao  from itest_tasks,itest_task_users,itest_users,itest_task_types WHERE itest_tasks.id = itest_task_users.task_id and  itest_task_users.user_id = itest_users.id and itest_task_types.id = itest_tasks.type_id and  itest_task_types.parent_id='1'  and itest_tasks.project_id in (1000,1036,1039,1040,1041,1043,1044,1045,1046,1047) and complete_time >= -28800 and complete_time <= 1374076800  GROUP BY itest_tasks.project_id;

SELECT project_id,count(FROM_UNIXTIME(execute_time, '%Y-%m-%d'))  sum_task_case_num,count(FROM_UNIXTIME(execute_time, '%Y-%m-%d'))/count(DISTINCT(FROM_UNIXTIME(execute_time, '%Y-%m-%d')))  avg_task_case_num from itest_tasks,itest_task_cases WHERE itest_tasks.id = itest_task_cases.task_id  and project_id in (1000,1036,1039,1040,1041,1043,1044,1045,1046,1047) and execute_time >= -28800 and execute_time <= 1374076800  GROUP BY project_id;

//4、合并两个表
select user_id,time,sum(create_user_num) create_user_num,sum(update_user_num) update_user_num from (select create_user user_id,FROM_UNIXTIME(create_time, '%Y-%u') time,COUNT(create_user) create_user_num, 0 update_user_num from itest_cases WHERE create_user in ('aalizzlu','adychen') and create_time >= 963244800 and create_time <= 1374076800 GROUP BY create_user,time  UNION select update_user user_id,FROM_UNIXTIME(update_time, '%Y-%u') time,0 create_user_num,COUNT(update_user) update_user_num from itest_cases WHERE update_user in ('aalizzlu','adychen','angelahe','anglebao','arthurxia','benjamin','bettyzeng','blackqin','blairwang')  and update_time >= 963244800 and update_time <= 1374076800 GROUP BY update_user,time ) as un_table GROUP BY user_id,time;

//5、星期统计 并分组
select execute_user,FROM_UNIXTIME(execute_time, '%Y-%u') time,count((case when itest_task_cases.execute_result='1' THEN execute_result END)) execute_result_1,count((case when itest_task_cases.execute_result='2' THEN execute_result END)) execute_result_2,count((case when itest_task_cases.execute_result='3' THEN execute_result END)) execute_result_3 from  itest_task_cases WHERE  execute_user in ('aalizzlu','adychen','angelahe','anglebao','arthurxia','benjamin','bettyzeng','blackqin','blairwang')  and execute_time >= 963244800 and execute_time <= 1374076800 GROUP BY execute_user,time;

select user_id,FROM_UNIXTIME(complete_time, '%Y-%u') time,count(case when itest_task_bugs.bug_id is not null then itest_task_users.task_id end) has_bug_task_num,count(case when itest_task_bugs.bug_id is null then itest_task_users.task_id end) no_bug_task_num,sum(spend) spend,sum((case when itest_task_types.parent_id='2' THEN spend else 0 END)) design_spend from itest_task_users LEFT OUTER JOIN itest_task_bugs on itest_task_users.task_id=itest_task_bugs.task_id  LEFT OUTER JOIN itest_tasks on itest_task_users.task_id=itest_tasks.id LEFT OUTER JOIN itest_task_types on itest_task_types.id=itest_tasks.type_id WHERE itest_task_users.state='complete'  and user_id in ('aalizzlu','zeeliang')  and complete_time >= 963244800 and complete_time <= 1374076800 GROUP BY user_id,time;