mysql - 如果count=0，如何包含SQL计数的结果？

Question

我有一个包含等级(1-6)的表格，如下所示:

mysql> SELECT two FROM data  WHERE date >= '2018-10-23 00:00:00' AND date < '2018-10-23 23:59:59';
+------+
| two  |
+------+
| 5    |
| 1    |
| 1    |
| 2    |
| 1    |
| 2    |
| 2    |
| 1    |
| 1    |
+------+
9 rows in set (0.00 sec)

我需要每个等级的出现次数，如下所示:

mysql> SELECT two, COUNT(1) as count  from data lookup where date >= '2018-10-23 00:00:00' AND date < '2018-10-23 23:59:59' GROUP BY two;
+------+-------+
| two  | count |
+------+-------+
| 1    |     5 |
| 2    |     3 |
| 5    |     1 |
+------+-------+
3 rows in set (0.00 sec)

但是我想包含成绩，即使它们不存在于选择中，例如:

+------+-------+
| two  | count |
+------+-------+
| 1    |     5 |
| 2    |     3 |
| 3    |     0 |
| 4    |     0 |
| 5    |     1 |
| 6    |     0 |
+------+-------+
6 rows in set (0.00 sec)

我见过使用 LEFT JOIN 和查找表的解决方案，但它们对于我的 SQL 初学者来说太复杂了，无法解决。

我制作了一个查找表(noten=grades):

mysql> SELECT * from lookup;
+-------+
| noten |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
|     6 |
+-------+
6 rows in set (0.00 sec)

但我无法正确理解...

mysql> SELECT two, COUNT(1) as count  FROM data LEFT JOIN lookup on two=lookup.noten  WHERE date >= '2018-10-23 00:00:
+------+-------+
| two  | count |
+------+-------+
| 1    |     5 |
| 2    |     3 |
| 5    |     1 |
+------+-------+
3 rows in set (0.00 sec)

非常感谢您的帮助!!!

Answer 1

您将使用左连接。诀窍是让 count() 和日期条件正确。我会把它写成:

SELECT l.noten, COUNT(d.noten) as count
FROM lookup l LEFT JOIN
     data d
     ON d.noten = l.noten AND
        d.date >= '2018-10-23' AND d.date < '2018-10-24'
GROUP BY l.noten
ORDER BY l.noten;

特别要注意的是，日期逻辑不需要时间组件来完成您想要的任务。