mysql - 即使 count(row) 为零，如何从表中获取数据？请查看描述以了解更多详细信息和查询？

Question

表一

id   mandal_name1    mandal12    mandal23    mandal3

table address

id   mandal_name   date1    mandal1       2017-07-11 12:34:112    mandal1       2017-07-11 12:54:453    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 , mandal10 , mandal20 , mandal3...

Answer 1

在这种情况下，关键是使用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 | yesterday_count ||-------------|-----------------||     mandal1 |               3 ||     mandal2 |               0 ||     mandal3 |               0 |

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

顺便说一句 - 不要使用DATE(date)，因为它使得无法在date列上使用索引，从而有效地导致全表扫描。