/*原型*/
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;
}