sql时间戳按月份聚合
首页 > >    作者:lininn   2019年4月9日 10:04 星期二   热度:1082°   百度已收录  
时间:2019-4-9 10:04   热度:1082° 

create_time为时间格式(字段名create_time 根据自己表字段修改,数据库中存为201610071202)

SELECT DATE_FORMAT(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks; SELECT DATE_FORMAT(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days; SELECT DATE_FORMAT(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months;

create_time为时间戳格式(字段名create_time 根据自己表字段修改,数据库中存为1474959640):


SELECT FROM_UNIXTIME(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks; SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days; SELECT FROM_UNIXTIME(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months;




var sqls={
//查询文章 多表join连接
articleSql:"select a.*,b.*,c.username from emlog_blog as a left JOIN emlog_sort AS b on a.sortid =b.sid LEFT JOIN emlog_user as c ON a.author=c.uid WHERE checked='y' and hide='n' order by date desc limit ?,?;select count(*) as total from emlog_blog;SELECT gid,title,views FROM emlog_blog ORDER BY views desc limit 0,5;",
//查询分类 按分类聚合
tagSql:"SELECT COUNT(*) as count,sid,sortname FROM emlog_sort as a,emlog_blog as b WHERE a.sid=b.sortid GROUP BY sortname order by count desc;"
};
module.exports=sqls;

    

mysql中本身支持一种更好的方法来达到上面的效果,那就是使用SQL_CALC_FOUND_ROWS和FOUND_ROWS()函数。见:http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
应用举例:

mysql> 
SELECT SQL_CALC_FOUND_ROWS * FROM 
tbl_name
1

    -> 
WHERE id > 100 LIMIT 10;
1

mysql> 
SELECT FOUND_ROWS();

二维码加载中...
本文作者:lininn      文章标题: sql时间戳按月份聚合
本文地址:?post=325
版权声明:若无注明,本文皆为“覆手为雨”原创,转载请保留文章出处。
分享本文至:

返回顶部    首页    手机版本    后花园   会员注册   
版权所有:覆手为雨    站长: lininn