mysql嵌套子查询之数据统计

日期:2018-12-18 浏览:362次

统计限制时间范围每天的数据,sql语句如下:

SELECT t.extend_time, COUNT(*) AS total FROM (SELECT DATE_FORMAT(extend_time, ‘%Y-%m-%d’) AS extend_time, need_nums FROM table WHERE appid = ‘***’ AND act_id = ‘6’ AND need_nums < ‘7’ AND extend_time BETWEEN ‘2018-12-15 16:08:24’ AND ‘2018-12-18 16:08:24’) t GROUP BY t.extend_time ORDER BY t.extend_time;

拆分出来,外层语句:

SELECT extend_time, COUNT(*) AS total FROM table GROUP BY extend_time ORDER BY extend_time;

外层是根据时间进行分组(GROUP BY extend_time)和分组总数计算(ORDER BY extend_time);

内层语句:

SELECT DATE_FORMAT(extend_time, ‘%Y-%m-%d’) AS extend_time, need_nums FROM table WHERE appid = ‘***’ AND act_id = ‘6’ AND need_nums < ‘7’ AND extend_time BETWEEN ‘2018-12-15 16:08:24’ AND ‘2018-12-18 16:08:24’;

查询出符合条件的数据,用于外层分组和计算数据;

没有评论

发表评论