Mysql 在一张表上加入日期

Question

我有一张名为seismik的表

╔═══════════════╦══════════╗
║      date     ║ type     ║ 
╠═══════════════╬══════════╣
║   2019-04-01  ║  rock    ║ 
║   2019-04-01  ║  water   ║
║   2019-04-01  ║  water   ║
║   2019-04-02  ║  rock    ║
║   2019-04-02  ║  rock    ║
║   2019-04-03  ║  water   ║
║   2019-04-03  ║  water   ║
║   2019-04-04  ║  rock    ║
╚═══════════════╩══════════╝

我想要做的是计算每个日期中有多少类型的 rock，但我希望没有 rock 的日期仍然存在并且值为 null。

所以我使用这个查询

SELECT date,type, COUNT(`type`) AS `freq`
FROM seismik 
WHERE type = "rock"
GROUP BY date_time

结果变成了

╔═══════════════╦══════════╦═════════╗
║      date     ║ type     ║ freq    ║ 
╠═══════════════╬══════════╬═════════╣
║   2019-04-01  ║  rock    ║    1    ║
║   2019-04-02  ║  rock    ║    2    ║
║   2019-04-04  ║  rock    ║    1    ║
╚═══════════════╩══════════╩═════════╝

我所期望的是

╔═══════════════╦══════════╦═════════╗
║      date     ║ type     ║ freq    ║ 
╠═══════════════╬══════════╬═════════╣
║   2019-04-01  ║  rock    ║    1    ║
║   2019-04-02  ║  rock    ║    2    ║
║   2019-04-03  ║  null    ║   null  ║ <- This
║   2019-04-04  ║  rock    ║    1    ║
╚═══════════════╩══════════╩═════════╝

Answer 1

使用case when并删除where条件

<强> DEMO

 SELECT dateval,max(case when types='rock' then types end) types, COUNT(case when `types`='rock' then 1 end) AS `freq`
 FROM t1 
 GROUP BY dateval

输出:

dateval     types   freq
2019-04-01  rock    1
2019-04-02  rock    2
2019-04-03          0
2019-04-04  rock    1