mysql 实用查询

按天分组查询

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; 

参考http://solitary.iteye.com/blog/1542903

精确到分钟的分组

SELECT HOUR(created_time) AS h, FLOOR(MINUTE(created_time) / 30) AS v, COUNT(*)
FROM record
WHERE created_time >= '2010-02-10' AND created_time < FROM_DAYS(TO_DAYS('2010-02-10')   1)
GROUP BY h, v;

count 中distinct的用法

SELECT COUNT( DISTINCT id ) FROM tablename;

长整型转换为整形

 cast(sum(data) as unsigned)

调整历史记录到5分钟纬度

 update login set dtdatetime = from_unixtime(floor(unix_timestamp(dtdatetime)/300)*300); 

防止重复递交

insert into t_test set ikey=1,value='a',value2=1 on duplicate key update value2=value2+1;

UNIQUE KEY `domain` (`domain`,`report_date`)