mysql - 即使count(row)为零，如何从表中获取数据？请参阅说明以获取更多详细信息和查询？

表一

id   mandal_name
1    mandal1
2    mandal2
3    mandal3

table address

id   mandal_name   date
1    mandal1       2017-07-11 12:34:11
2    mandal1       2017-07-11 12:54:45
3    mandal1      2017-07-11 12:23:23

SELECT count(id) as yesterday_count, mandal FROM address WHERE date(date) = '2017-07-11'

结果明显

3，mandal1


预期结果

3，mandal1
0，mandal2
0，mandal3
...

最佳答案

关键是在这种情况下使用OUTER JOIN-LEFT JOIN。

你可以做

SELECT m.mandal_name, COUNT(a.id) AS yesterday_count
  FROM  table_one m LEFT JOIN address a
    ON m.mandal_name = a.mandal_name
   AND a.date >= '2017-07-11'
   AND a.date < '2017-07-12'
 GROUP BY m.mandal_name;

要么

SELECT m.mandal_name, COALESCE(count, 0) AS yesterday_count
  FROM  table_one m LEFT JOIN (
    SELECT mandal_name, COUNT(*) AS count
      FROM address
     WHERE date >= '2017-07-11'
       AND date < '2017-07-12'
  ) a
    ON m.mandal_name = a.mandal_name;

这是一个SQLFiddle演示

输出量

| mandal_name |昨天计数|
| ------------- || ----------------- |
| mandal1 | 3 |
| mandal2 | 0 |
| mandal3 | 0 |


进一步阅读-A Visual Explanation of SQL Joins

附带说明-不要使用DATE(date)，因为它不可能在date列上使用索引，从而无法有效地进行全表扫描。