当先锋百科网

首页 1 2 3 4 5 6 7

mysql函数date_format统计刷选按年月日统计的数据 

/*原型*/
SELECT
	count(did) AS sum,
	date_format(releasetime, '%Y-%m-%d') AS releasetime
FROM hengtu_demand
WHERE 1
GROUP BY
	date_format(releasetime, '%Y-%m-%d')

/*按年分组  @参数 需求id sid=1*/
SELECT
	b.sid,
	b.`name` as sname,
	count(did) AS value,
　　　　 date_format(releasetime, '%Y') AS name
FROM
	hengtu_demand a
LEFT JOIN hengtu_service b ON a.sid = b.sid
WHERE
	b.`status` = 1
AND b.sid = 1
GROUP BY
	date_format(a.releasetime, '%Y')


/*按月分组  @参数 需求id sid=1  @参数 年份 2014*/
SELECT
	b.sid,
	b.`name` as sname,
	count(did) AS value,
	date_format(releasetime, '%Y') AS year,
	date_format(releasetime, '%m') AS month,
	date_format(releasetime, '%m') AS name
FROM
	hengtu_demand a
LEFT JOIN hengtu_service b ON a.sid = b.sid
WHERE
	b.`status` = 1
AND b.sid = 1
GROUP BY
	date_format(a.releasetime, '2014-%m')


/*按日分组  @参数 需求id sid=1  @参数 年份 2014  @参数 月份 1月*/
SELECT
	b.sid,
	b.`name` as sname,
	count(did) AS value,
	date_format(releasetime, '2014') AS year,
	date_format(releasetime, '11') AS month,
	date_format(releasetime, '%d') AS day,
	date_format(releasetime, '%d') AS name
FROM
	hengtu_demand a
LEFT JOIN hengtu_service b ON a.sid = b.sid
WHERE
	b.`status` = 1
AND b.sid = 1
GROUP BY
	date_format(a.releasetime, '2014-11-%d')


/**按年月查询加入where条件  正确标准语句*/
SELECT
	b.sid,
	b.`name` AS sname,
	count(did) AS value,
	date_format(releasetime, '%Y') AS year,
	date_format(releasetime, '%m') AS month,
	date_format(releasetime, '%d') AS day,
	date_format(releasetime, '%d') AS name
FROM
	hengtu_demand a
LEFT JOIN hengtu_service b ON a.sid = b.sid
WHERE
	b.`status` = 1
AND b.sid = ".$sid."
AND date_format(releasetime, '%m') = ".$month."
GROUP BY
	date_format(
		a.releasetime,
		'".$year."-".$month."-%d'
	)

/*
*如果数据库时间是以时间戳格式储存的就使用form_unixtime函数

　　使用示例

 /*
    *使用需求榜 总报表 第一步
    */ 
    function headreport(){
        $demandsortcount=M()->query("SELECT b.sid,b.`name`,COUNT('b.name') AS value 
　　　　　　　　　　　　　　　FROM hengtu_demand a 
　　　　　　　　　　　　　　　LEFT JOIN hengtu_service b ON a.sid = b.sid 
　　　　　　　　　　　　　　　WHERE b.`status` = 1 GROUP BY b.`name` ORDER BY value DESC");
         return $demandsortcount;
    }
     /*
    *使用需求榜 总报表 第二步 按年份分组
    *@param 需求id  sid=1
    */ 
    function yearreport($sid=''){
        $demandsortcount=M()->query("SELECT b.sid,b.`name` as sname,count(did) AS value,date_format(releasetime, '%Y') AS year,
　　　　　　　　　　　　　　　date_format(releasetime, '%Y') AS name FROM hengtu_demand a 
            　　　　　　　　LEFT JOIN hengtu_service b ON a.sid = b.sid 
　　　　　　　　　　　　　　　WHERE b.`status` = 1 AND b.sid = ".$sid." 
　　　　　　　　　　　　　　　GROUP BY date_format(a.releasetime, '%Y')");
         return $demandsortcount;
    }
    /*
    *使用需求榜 总报表 第二步 按月份分组
    *@param 需求id  sid=1
    *@param 年份    year=2014
    */ 
    function monthreport($sid='',$year=''){
        $demandsortcount=M()->query("SELECT b.sid,b.`name` AS sname,count(did) AS value,date_format(releasetime, '%Y') AS year,
　　　　　　　　　　　　　　 date_format(releasetime, '%m') AS month,date_format(releasetime, '%m') AS name FROM hengtu_demand a 
　　　　　　　　　　　　　　 LEFT JOIN hengtu_service b ON a.sid = b.sid 
　　　　　　　　　　　　　　 WHERE b.`status` = 1 AND b.sid = ".$sid." AND date_format(releasetime, '%Y') = ".$year." 
　　　　　　　　　　　　　　 GROUP BY date_format(a.releasetime,'%Y-%m')");
         return $demandsortcount;
    }
    /*
    *使用需求榜 总报表 第二步 按日份分组
    *@param 需求id  sid=1
    *@param 年份    year=2014
    *@param 月份    month=3月
    */ 
    function dayreport($sid='',$year='',$month=''){
        $demandsortcount=M()->query("SELECT b.sid,b.`name` AS sname,count(did) AS value,date_format(releasetime, '%Y') AS year,
　　　　　　　　　　　　　　　date_format(releasetime, '%m') AS month,date_format(releasetime, '%d') AS day,
　　　　　　　　　　　　　　　date_format(releasetime, '%d') AS name FROM hengtu_demand a 
　　　　　　　　　　　　　　　LEFT JOIN hengtu_service b ON a.sid = b.sid 
　　　　　　　　　　　　　　　WHERE b.`status` = 1 AND b.sid = ".$sid." AND date_format(releasetime, '%Y') = ".$year." AND date_format(releasetim　　　　　　　　　　　　　　　e, '%m') = ".$month." 
　　　　　　　　　　　　　　　GROUP BY date_format(a.releasetime,'%Y-%m-%d')");
         return $demandsortcount;
    }

 

转载于:https://www.cnblogs.com/binggozhou/p/4165237.html