mysql - php可以按天连续去两周的更新数据吗

Question

use_id reg_time
1      2015-12-01 
2      2015-12-02 
3      2015-12-02 
4      2015-12-03 
5      2015-12-03 
6      2015-12-03 
7      2015-11-30   

要得到
`

reg_time    count count(2周内)
2015-12-01   0    0
2015-11-29   0    0
2015-11-30   1    1
2015-11-31   0    1
2015-12-01   1    2
2015-12-02   2    4
2015-12-03   3    7

`

目的就是取出当天注册 以及当天往前推14天的注册数量

现在的做法是循环count
`

$lasttime=strtotime(date("Y-m-d",time()))-14*3600*24;
$firsttime=strtotime(date("Y-m-d",time()))+3600*24;
$result = array()
for($i=13;$i>=0;$i--){
    $first=date("Y-m-d",$firsttime-$i*3600*24);
    $last=date("Y-m-d",$lasttime-$i*3600*24);
    $today=date("Y-m-d",time()-$i*24*3600);
    $tmp1 = "select count(1) from table where reg_time > $last and reg_time < $last";
    $tmp2 = "select count(1) from table where reg_time = $today "
    $result[][date] =$today;
    $result[][count1] =$tmp1;
    $result[][count2] =$tmp2;
    
    }

`

这样做的结果就是一共执行了28次count 感觉效率相当低下 求优化思路

Answer 1

select reg_time,count(1) from table where reg_time > $last and reg_time < $last group by reg_time

用sql分组查询

Answer 2

针对你的问题，建议你直接获取每天的更新数据，在PHP自己加一下。这样SQL能减少到只剩一个。