php - mysql left join 如果行不存在，则使用 0

Question

我正在尝试获取具有平均参与率的表格的日期列表。但目前，如果媒体表中不存在日期，则会从列表中略读日期。因此，如果该行不存在，我希望参与率为 0。

例如(当前)

2017-09-30 - 123
2017-09-28 - 1234
2017-09-27 - 12345

等等

但应该是

2017-09-30 - 123
2017-09-29 - 0
2017-09-28 - 1234
2017-09-27 - 12345

这是我的查询

"select d.date AS created_at,
                AVG((IFNULL(v.likes, 0) + IFNULL(v.comments, 0)) /
                IFNULL((SELECT count FROM followers prev WHERE DATE(prev.created_at) = DATE(v.created_at)
                                                  AND prev.profile_id = '".$this->profile->id."'
                                                  ORDER BY created_at
                                                  DESC LIMIT 1), 0) * 100)
                                                  AS count
                from
                (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) date from
                 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
                 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
                 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
                 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
                 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) d
                left join media v on d.date = DATE(v.created_at)
                where d.date between '".Carbon::createFromFormat('Y-m-d', $this->startDate)->toDateTimeString()."' and '".Carbon::createFromFormat('Y-m-d', $this->endDate)->toDateTimeString()."'
                AND v.profile_id = '".$this->profile->id."'
                group by d.date
                order by d.date DESC"

Answer 1

要获得真正的 LEFT JOIN 结果，请将表 v 条件从 WHERE 移动到 ON 子句:

        left join media v on d.date = DATE(v.created_at) AND v.profile_id = '".$this->profile->id."'
        where d.date between '".Carbon::createFromFormat('Y-m-d', $this->startDate)->toDateTimeString()."' and '".Carbon::createFromFormat('Y-m-d', $this->endDate)->toDateTimeString()."'