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));
    }

{
  "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"
  }]
}