mysql聚合函数 时间格式化 关联查询

1.时间格式化：SELECT DATE_FORMAT(a.START_TIME, '%Y-%m-%d' ) datime,DATE_FORMAT(a.START_TIME, '%Y-%m' ) motime FROM AGENT_STATUS a;

2.聚合函数：SELECT datime,motime,SUM(CASE WHEN STATUS = '102' THEN 1 ELSE 0 END) jtcount,SUM(CASE WHEN STATUS = '102' THEN DURATION 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.复杂关联查询：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 = ? AND  a.START_TIME > ? AND  a.START_TIME <= ? 

SELECT * FROM (SELECT * FROM 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. 数据表添加字段：ALTER TABLE table_name ADD 字段名 INT(11) DEFAULT '1';

ALTER TABLE table_name ADD 字段名 INT(11) NOT NULL;

5.修改表名：ALTER  TABLE table_name RENAME TO table_name_new;