Tp5+mysql按年季度月周日小時查詢時無數據的時間段補0方法 · 雜記

### 需求描述：項目中首頁用幾個圖表來展示銷售數據，根據不同的時間范圍、時間維度來查詢，有些時間段沒有數據的，圖表中各項數據顯示為0。 ``` /** * * 終端報表 * User: wenhu8000 */ function getReport() { #--獲取公共參數 $d = $this->getCommonData();#halt($d); #--查詢條件 /*$where['RD.i_code'] = ['>', -1];*/ #--排序 $order = "total DESC"; #--時間范圍 if (!empty($d['range'])) { $time = $this->getTimeRange($d['range']); $d['staTime'] = $time['staTime']; $d['endTime'] = $time['endTime']; $order = "times"; } #--時間維度 if (!empty($d['times'])) { $times = $this->getTimes($d['times']); $order = "times"; } else { $times = "%Y-%m"; $d['times'] = "month"; } #--計算出起始時間按不同時間維度的差，最小為 1 $limit = Db::query("SELECT TIMESTAMPDIFF(".$d['times'].",?,?) as num from table1 limit 1",[$d['staTime'],$d['endTime']])[0]['num']?:1;#halt($limit); #--查詢數據 $sql = Db::table("(SELECT DATE_FORMAT(@date := DATE_ADD(@date, INTERVAL + 1 " . $d['times'] . "),'" . $times . "') times FROM ( SELECT @date := DATE_ADD('" . $d['staTime'] . "', INTERVAL - 1 " . $d['times'] . ") FROM rdr1 LIMIT " . $limit . " ) time)") ->alias('TI') ->field("TI.times,COUNT(OR.field2) as orderNum,SUM(OR.total) as total,COUNT(DISTINCT RD.i_code) as iNum") ->join('table1 I', "I.s_code='". $this->aInfo["shop_code"] ."'", 'LEFT') ->join('table2 OR', "INSTR(OR.datefield,TI.times)>0 && OR.datefield BETWEEN '" . $d['staTime'] . "' and '" . $d['endTime'] . "'", 'LEFT') ->join('table3 RD', "RD.field1=OR.field2", 'LEFT') ->group("times") ->order($order) ->buildSql(); #halt($sql); $res = Db::table($sql)->alias('TT')->paginate($d['limit'], $d['paginate'], ['page' => $d['page']])->toArray(); #halt($res); #--返回結果 if (empty($res['data'])) { return ['code' => '1', 'data' => [], 'msg' => '無數據！']; } else { #--合計 $res['orderSum'] = array_sum(array_column($res['data'], 'orderNum')); $res['totalSum'] = array_sum(array_column($res['data'], 'total')); $res['iSum'] = array_sum(array_column($res['data'], 'iNum')); return ['code' => '0', 'data' => $res]; } } /** * * 獲取特定時間范圍用于查詢 * User: wenhu8000 * @name: 時間范圍名稱 */ function getTimeRange($name) { #--時間范圍 switch (true) { #--去年 case $name === "lastYear": $d['staTime'] = date('Y-01-01 00:00:00', strtotime('-1 year')); $d['endTime'] = date('Y-01-01 00:00:00', strtotime('+0 year')); break; #--上季度 case $name === "lastQuarter": $num = (ceil(date("n") / 3) * 3 - 3) + 1; $d['staTime'] = date('Y-m-01 00:00:00', strtotime('-' . (date("n") - $num + 3) . " month")); $d['endTime'] = date('Y-m-01 00:00:00', strtotime('+' . ($num - date("n")) . " month")); break; #--上月 case $name === "lastMonth": $d['staTime'] = date('Y-m-01 00:00:00', strtotime('-1 month')); $d['endTime'] = date('Y-m-01 00:00:00', strtotime('+0 month')); break; #--上周 case $name === "lastWeek": $d['staTime'] = date("Y-m-d 00:00:00", strtotime(date("Y") . 'W' . (date("W") - 1))); $d['endTime'] = date("Y-m-d 00:00:00", strtotime(date("Y") . 'W' . date("W"))); break; #--昨天 case $name === "yesterday": $d['staTime'] = date('Y-m-d 00:00:00', strtotime('-1 day')); $d['endTime'] = date('Y-m-d 00:00:00', strtotime('+0 day')); break; #--今年 case $name === "year": $d['staTime'] = date('Y-01-01 00:00:00', strtotime('+0 year')); $d['endTime'] = date('Y-01-01 00:00:00', strtotime('+1 year')); break; #--本季度 case $name === "quarter": $num = (ceil(date("n") / 3) * 3 - 3) + 1; $d['staTime'] = date('Y-m-01 00:00:00', strtotime('-' . (date("n") - $num) . " month")); $d['endTime'] = date('Y-m-01 00:00:00', strtotime('+' . ($num + 3 - date("n")) . " month")); break; #--本月 case $name === "month": $d['staTime'] = date('Y-m-01 00:00:00', strtotime('+0 month')); $d['endTime'] = date('Y-m-01 00:00:00', strtotime('+1 month')); break; #--本周 case $name === "week": $d['staTime'] = date("Y-m-d 00:00:00", strtotime(date("Y") . 'W' . date("W"))); $d['endTime'] = date("Y-m-d 00:00:00", strtotime(date("Y") . 'W' . (date("W") + 1))); break; #--默認返回當天范圍 default: $d['staTime'] = date('Y-m-d 00:00:00', strtotime('+0 day')); $d['endTime'] = date('Y-m-d 00:00:00', strtotime('+1 day')); } return $d; } /** * * 獲取不同時間維度，用于分組查詢 * User: wenhu8000 * @name: 時間維度名稱 */ function getTimes($name) { #--時間維度 switch (true) { #--按月 case $name === "month": $d = "%Y-%m"; break; #--按周 case $name === "week": $d = "%Y-%v"; break; #--按日 case $name === "day": $d = "%Y-%m-%d"; break; #--按時 case $name === "hour": $d = "%Y-%m-%d %H"; break; #--默認按月 default: $d = "%Y-%m"; } return $d; } ```