MYSQL-统计查询

<pre name="code" class="html"><span style="font-size:14px;"> MYSQL-统计查询

mysql查询统计报表
获取当前时间截
<span style="font-size:18px;color:#ff0000;"><strong>[html] view plaincopyprint?
select UNIX_TIMESTAMP(NOW())  </strong></span>

获取当天零时时间截
[html] view plaincopyprint?
select UNIX_TIMESTAMP(curdate())  

统计当天每个小时段的记录数量
[html] view plaincopyprint?
SELECT COUNT(*) as statnum, DATE_FORMAT(FROM_UNIXTIME(created),'%H') as time FROM qxm30_point_log WHERE created > unix_timestamp(curdate()) GROUP BY  DATE_FORMAT(FROM_UNIXTIME(created),'%H')  

统计当天每个小时段的用户数，去重复用户记录数 DISTINCT修饰去重复
[html] view plaincopyprint?
SELECT COUNT(distinct uid) as statnum, DATE_FORMAT(FROM_UNIXTIME(created),'%H') as time FROM qxm30_point_log WHERE created > unix_timestamp(curdate()) GROUP BY  DATE_FORMAT(FROM_UNIXTIME(created),'%H')  

统计某月每天的记录数量
[html] view plaincopyprint?
SELECT COUNT(*) as statnum, DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d') as time FROM qxm30_point_log WHERE created BETWEEN  unix_timestamp('2013-9-11') AND (unix_timestamp('2013-9-11')+3600*24*30) GROUP BY  DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d')  
统计某月每天的用户数量
[html] view plaincopyprint?
SELECT COUNT(distinct uid) as statnum, DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d') as time FROM qxm30_point_log WHERE created BETWEEN  unix_timestamp('2013-9-11') AND (unix_timestamp('2013-9-11')+3600*24*30) GROUP BY  DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d')  

统计某月每天的人均记录数量
[html] view plaincopyprint?
SELECT count(*)/COUNT(distinct uid) as statnum, DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d') as time FROM qxm30_point_log WHERE created BETWEEN  unix_timestamp('2013-9-11') AND (unix_timestamp('2013-9-11')+3600*24*30) GROUP BY  DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d')  

另附图表插件highcharts
下载地址：http://www.highcharts.com/download
 
  
 
 

版权声明：本文为博主原创文章，未经博主允许不得转载。
上一篇SQL 时间截按月分组查询
下一篇django-cms学习笔计（一）
顶
2
踩</span>