MySQL实战基础知识入门(3)：近7日销量合计php后端mysql语句如果当日为空则自动补0的解决方案

public function getEveryDay() { global $db, $res; dbc(); $NowTimes = date("Y-m-d", time()); $sql = "select DATE_FORMAT(paid_at,'%Y-%m-%d') as dataKey,ifnull(COUNT(id),0) AS totalTrans,ifnull(SUM(should_paid),0) AS totalPays from ( SELECT \"" . $NowTimes . "\" as evData
                union all SELECT date_sub(\"" . $NowTimes . "\", interval 1 day) as evData
                union all SELECT date_sub(\"" . $NowTimes . "\", interval 2 day) as evData
                union all SELECT date_sub(\"" . $NowTimes . "\", interval 3 day) as evData
                union all SELECT date_sub(\"" . $NowTimes . "\", interval 4 day) as evData
                union all SELECT date_sub(\"" . $NowTimes . "\", interval 5 day) as evData
                union all SELECT date_sub(\"" . $NowTimes . "\", interval 6 day) as evData) as total LEFT JOIN " . $db->table('orders') . " ev on DATE_FORMAT(ev.paid_at,'%Y-%m-%d') = total.evData "; $sql .= " GROUP BY evData"; $row = $db->queryall($sql); $res["data"] = $row; die(json_encode_lockdata($res)); } 

JSON数据格式化

{ "err": "", "res": "", "data": [{ "dataKey": "2020-07-23", "totalTrans": "93", "totalPays": "3577.16" }, { "dataKey": "2020-07-24", "totalTrans": "106", "totalPays": "3313.11" }, { "dataKey": "2020-07-25", "totalTrans": "110", "totalPays": "3442.04" }, { "dataKey": "2020-07-26", "totalTrans": "96", "totalPays": "2890.99" }, { "dataKey": "2020-07-27", "totalTrans": "119", "totalPays": "3669.60" }, { "dataKey": "2020-07-28", "totalTrans": "135", "totalPays": "4262.56" }, { "dataKey": "2020-07-29", "totalTrans": "48", "totalPays": "1280.88" }] } 

Done!