1、说明：数据库采用Mysql 2、此次统计除了按指定间隔（比如7天）统计外，还需对业务数据进行处理，比如论坛中楼主回帖时间间隔等（比如2小时内回自复、一天内回复、超过一天回复等等），统计表形如下： 统计表 3、统计思路：分别构建三个子表，表示指定间隔中的2小时内回自复记录表、一天内回复记录表、超过一天回复记录表： 2小时内回自复记录 一天内回复记录表 超过一天回复记录 注：第一个子表，多一个start_time,是为最后的排序准备的！ 4、最后对以上三个子表进行关联查询，得到最终结果： 统计结果表 5、最终SQL语句: SELECT a.cycle, IFNULL(a.amount,0) AS two_hour_amount, IFNULL(b.amount,0) AS one_day_amount, IFNULL(c.amount,0) AS pass_one_day_amount FROM ( SELECT dt.cycle,dt.start_time,SUM(db.amount) AS amount FROM (SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -7 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d') AS end_time, "前一周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -14 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -7 DAY),'%Y-%m-%d') AS end_time, "前两周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -21 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -14 DAY),'%Y-%m-%d') AS end_time, "前三周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -28 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -21 DAY),'%Y-%m-%d') AS end_time, "前四周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -35 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -28 DAY),'%Y-%m-%d') AS end_time, "前五周" AS cycle ) dt LEFT JOIN (SELECT DATE_FORMAT(sub_time,'%Y-%m-%d') AS sub_time,COUNT(*) AS amount FROM deal WHERE TIMESTAMPDIFF(DAY,sub_time,NOW()) <= 35 GROUP BY sub_time,deal_time HAVING ((deal_time IS NULL AND TIMESTAMPDIFF(HOUR,sub_time,NOW()) <= 2) OR (deal_time IS NOT NULL AND TIMESTAMPDIFF(HOUR,sub_time,deal_time) <= 2)) ) db ON (db.sub_time>=dt.start_time AND db.sub_time < dt.end_time) GROUP BY dt.cycle,dt.start_time ) a LEFT JOIN (SELECT dt.cycle,SUM(db.amount) AS amount FROM (SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -7 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d') AS end_time, "前一周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -14 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -7 DAY),'%Y-%m-%d') AS end_time, "前两周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -21 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -14 DAY),'%Y-%m-%d') AS end_time, "前三周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -28 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -21 DAY),'%Y-%m-%d') AS end_time, "前四周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -35 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -28 DAY),'%Y-%m-%d') AS end_time, "前五周" AS cycle ) dt LEFT JOIN (SELECT DATE_FORMAT(sub_time,'%Y-%m-%d') AS sub_time,COUNT(*) AS amount FROM deal WHERE TIMESTAMPDIFF(DAY,sub_time,NOW()) <= 35 GROUP BY sub_time,deal_time HAVING ((deal_time IS NULL AND TIMESTAMPDIFF(HOUR,sub_time,NOW()) > 2 AND TIMESTAMPDIFF(DAY,sub_time,NOW())<= 1) OR (deal_time IS NOT NULL AND TIMESTAMPDIFF(HOUR,sub_time,deal_time) > 2 AND TIMESTAMPDIFF(DAY,sub_time,deal_time)<= 1)) ) db ON (db.sub_time >= dt.start_time AND db.sub_time < dt.end_time) GROUP BY dt.cycle ) b ON a.cycle = b.cycle LEFT JOIN (SELECT dt.cycle,SUM(db.amount) AS amount FROM (SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -7 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d') AS end_time, "前一周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -14 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -7 DAY),'%Y-%m-%d') AS end_time, "前两周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -21 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -14 DAY),'%Y-%m-%d') AS end_time, "前三周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -28 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -21 DAY),'%Y-%m-%d') AS end_time, "前四周" AS cycle UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -35 DAY),'%Y-%m-%d') AS start_time, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -28 DAY),'%Y-%m-%d') AS end_time, "前五周" AS cycle ) dt LEFT JOIN (SELECT DATE_FORMAT(sub_time,'%Y-%m-%d') AS sub_time,COUNT(*) AS amount FROM deal WHERE TIMESTAMPDIFF(DAY,sub_time,NOW()) <= 35 GROUP BY sub_time,deal_time HAVING ((deal_time IS NULL AND TIMESTAMPDIFF(DAY,sub_time,NOW()) > 1) OR (deal_time IS NOT NULL AND TIMESTAMPDIFF(DAY,sub_time,deal_time) > 1)) ) db ON (db.sub_time >= dt.start_time AND db.sub_time < dt.end_time) GROUP BY dt.cycle ) c ON a.cycle = c.cycle ORDER BY a.start_time DESC 以上的间隔都可以根据自己义务需要改动。 大功告成，哇咔咔……