mysql中的连续数据,MySQL中实现连续日期内数据统计,缺省天数0补全

某一日，需要查询订单表中一个月每天的金额数

查询出数据如下:

array(14) {

[0] => array(2) {

["money"] => string(7) "2000.00"

["times"] => string(2) "12"

}

[1] => array(2) {

["money"] => string(7) "6000.00"

["times"] => string(2) "14"

}

[2] => array(2) {

["money"] => string(8) "31569.00"

["times"] => string(2) "15"

}

[3] => array(2) {

["money"] => string(9) "134596.50"

["times"] => string(2) "16"

}

[4] => array(2) {

["money"] => string(7) "4000.00"

["times"] => string(2) "17"

}

[5] => array(2) {

["money"] => string(8) "20525.00"

["times"] => string(2) "18"

}

[6] => array(2) {

["money"] => string(7) "2000.00"

["times"] => string(2) "20"

}

[7] => array(2) {

["money"] => string(7) "6000.00"

["times"] => string(2) "21"

}

[8] => array(2) {

["money"] => string(7) "9000.00"

["times"] => string(2) "22"

}

[9] => array(2) {

["money"] => string(7) "2000.00"

["times"] => string(2) "23"

}

[10] => array(2) {

["money"] => string(7) "2000.00"

["times"] => string(2) "25"

}

[11] => array(2) {

["money"] => string(7) "7000.00"

["times"] => string(2) "26"

}

[12] => array(2) {

["money"] => string(8) "10000.00"

["times"] => string(2) "27"

}

[13] => array(2) {

["money"] => string(9) "212263.28"

["times"] => string(2) "28"

}

}

只查询了14天的数据,而且中间还有几天没有数据,但是我想要没查询的天数显示为0,这个时候就需要先给数据加个起点!如果没有1号,我们就把一号加上,有就略过! 然后还要加个终点,终点应放在原始数组中，不然算法不可能自作主张的添加数据,我这里默认每个月31号,毕竟要做折线统计图,日期是固定的,方法如下:

1 if ($list[0]['times'] !=1){2 array_unshift($list,array('money' => '0', 'times' =>'01')); //这样就添加了起点

3 }4

5 if ($list[count($list)-1]['times'] !=31){6 $list[] = array('money' => '0', 'times' => '31');//这样就添加了终点

7 }8

9

10 $last = '1';11

12 foreach($list as $r) {13 while($last && $last < $r['times']) {14 $res[] = array('money' => '0','times' => "$last");15

16 $last +=1;17 }18 $res[] = $r;19 $last +=1;20 }21

22

23 //$res就是最终的结果

24 dump($res);

结果如下:

array(31) {

[0] => array(2) {

["money"] => string(1) "0"["times"] => string(2) "01"}

[1] => array(2) {

["money"] => string(1) "0"["times"] => string(1) "2"}

[2] => array(2) {

["money"] => string(1) "0"["times"] => string(1) "3"}

[3] => array(2) {

["money"] => string(1) "0"["times"] => string(1) "4"}

[4] => array(2) {

["money"] => string(1) "0"["times"] => string(1) "5"}

[5] => array(2) {

["money"] => string(1) "0"["times"] => string(1) "6"}

[6] => array(2) {

["money"] => string(1) "0"["times"] => string(1) "7"}

[7] => array(2) {

["money"] => string(1) "0"["times"] => string(1) "8"}

[8] => array(2) {

["money"] => string(1) "0"["times"] => string(1) "9"}

[9] => array(2) {

["money"] => string(1) "0"["times"] => string(2) "10"}

[10] => array(2) {

["money"] => string(1) "0"["times"] => string(2) "11"}

[11] => array(2) {

["money"] => string(7) "2000.00"["times"] => string(2) "12"}

[12] => array(2) {

["money"] => string(1) "0"["times"] => string(2) "13"}

[13] => array(2) {

["money"] => string(7) "6000.00"["times"] => string(2) "14"}

[14] => array(2) {

["money"] => string(8) "31569.00"["times"] => string(2) "15"}

[15] => array(2) {

["money"] => string(9) "134596.50"["times"] => string(2) "16"}

[16] => array(2) {

["money"] => string(7) "4000.00"["times"] => string(2) "17"}

[17] => array(2) {

["money"] => string(8) "20525.00"["times"] => string(2) "18"}

[18] => array(2) {

["money"] => string(1) "0"["times"] => string(2) "19"}

[19] => array(2) {

["money"] => string(7) "2000.00"["times"] => string(2) "20"}

[20] => array(2) {

["money"] => string(7) "6000.00"["times"] => string(2) "21"}

[21] => array(2) {

["money"] => string(7) "9000.00"["times"] => string(2) "22"}

[22] => array(2) {

["money"] => string(7) "2000.00"["times"] => string(2) "23"}

[23] => array(2) {

["money"] => string(1) "0"["times"] => string(2) "24"}

[24] => array(2) {

["money"] => string(7) "2000.00"["times"] => string(2) "25"}

[25] => array(2) {

["money"] => string(7) "7000.00"["times"] => string(2) "26"}

[26] => array(2) {

["money"] => string(8) "10000.00"["times"] => string(2) "27"}

[27] => array(2) {

["money"] => string(9) "212263.28"["times"] => string(2) "28"}

[28] => array(2) {

["money"] => string(1) "0"["times"] => string(2) "29"}

[29] => array(2) {

["money"] => string(1) "0"["times"] => string(2) "30"}

[30] => array(2) {

["money"] => string(1) "0"["times"] => string(2) "31"}

}

同样的. 12个月也可以这样操作,只要把终点改为12就可以了