1. Time Format: SELECT date_format (a.start_time, '%y-%m-%d ') Datime,date_format (a.start_time, '%y-%m ') Motime from Agent_ STATUS A;
2. Aggregation function: SELECT datime,motime,sum (case is status = ' 102 ' then 1 ELSE 0 END) jtcount,sum (case is status = ' 102 ' then DURA tion ELSE 0 END) jtsum from ( SELECT date_format (a.start_time, '%y-%m-%d ') Datime,date_format (a.start_time, '%y-%m ') Motime, status,duration from Agent_status a) b GROUP by B.datime ORDER by B.datime DESC;
3. Complex correlation query: SELECT * from Table_one a LEFT join Table_two b on a.device_num = b.device_num_id) left join Table_three C on B. skill_group_id = c.id) WHERE c.user_id =? > a.start_time? and A.start_time <=?
SELECT * FROM (SELECT * to Table_one where 1=1) a left JOIN (select * from Table_two WHERE 1=1) b on a.id = b.id where 1 =1
4. Data table Add Field: ALTER table table_name add field name INT (one) DEFAULT ' 1 ';
ALTER TABLE table_name ADD field name INT (one) not NULL;
5. Modify table name: ALTER table table_name RENAME to table_name_new;
MySQL aggregate function Time Format Association query