如果count=0,如何包含sql count的结果?

ymzxtsji  于 2021-06-18  发布在  Mysql
关注(0)|答案(4)|浏览(358)

我有一张1-6年级的table,比如:

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)

我见过使用左联接表和查找表的解决方案,但对于我的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)

非常感谢您的帮助!!!

a11xaf1n

a11xaf1n1#

发布了@gordon linoff的稍微修改过的版本,成功了。谢谢大家!

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

4ngedf3f2#

你会使用 left join . 诀窍是得到 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;

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

kwvwclae

kwvwclae3#

您可以在查询本身中使用 Union All ,并将其用作派生表。
你需要做一个 Left Join 从这个查找表到 data table。
请注意,使用左联接时,应在中指定右侧表上的任何条件 On 子句,而不是 Where . 如果它们在 Where ,它将限制您的结果,并有效地使其仅成为内部联接。
请尝试以下操作:

SELECT agl.grade, COUNT(d.two) FROM 
(SELECT 1 AS grade UNION ALL 
 SELECT 2 UNION ALL
 SELECT 3 UNION ALL
 SELECT 4 UNION ALL
 SELECT 5 UNION ALL
 SELECT 6) AS agl
LEFT JOIN data AS d ON d.two = agl.grade AND 
                  d.date >= '2018-10-23 00:00:00' AND 
                  d.date < '2018-10-23 23:59:59' 
GROUP BY agl.grade
ny6fqffe

ny6fqffe4#

一种选择是使用 INFORMATION_SCHEMA.CHARACTER_SETScross/left joins 递归为:

select q.rn as two, 
       count(coalesce(t.two)) as count
  from
    (  
    select @rownum := @rownum + 1 rn
      from (select @rownum := 0) t
      cross join INFORMATION_SCHEMA.CHARACTER_SETS 
     limit 6 
    ) q 
  left join data t
    on ( t.two = q.rn and date >= '2018-10-23 00:00:00' and date < '2018-10-23 23:59:59' )
 group by rn    
 order by rn;

 two    count
  1       5
  2       3
  3       0
  4       0
  5       1
  6       0

rextester演示

相关问题