/*原型*/
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函数
*/
扫一扫
2544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
