sql - 从数据库表中不存在的数据向查询添加新行

Question

我有以下示例表:

+----------+------+-------+
|   DATE   | NAME | HOURS |
+----------+------+-------+
| 2018-5-3 | JOHN |   8   |
+----------+------+-------+
| 2018-5-9 | JOHN |   5   |
+----------+------+-------+

我如何生成一个查询来填充现有数据的新行，例如示例查询结果:

+-----------+------+-------+
|   DATE    | NAME | HOURS |
+-----------+------+-------+
| 2018-5-1  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-2  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-3  | JOHN |   8   |
+-----------+------+-------+
| 2018-5-4  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-5  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-6  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-7  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-8  | JOHN |   0   |
+-----------+------+-------+
| 2018-5-9  | JOHN |   5   |
+-----------+------+-------+
| 2018-5-10 | JOHN |   0   |
+-----------+------+-------+

检查我是否在 HOURS 列中添加了 0，因为 JOHN 没有出现在指定日期的小时数(仅在 2018-5-3 和 2018-5-8)。我目前正在尝试获得此结果。这只是我需要处理的大表的开始，所以我需要为每个用户生成这个固定值。我尝试对之前生成的日期使用左/右连接，但没有成功。

你能告诉我完成它的最佳方法吗？谢谢。

Answer 1

使用generate_series() 和left join:

select g.dte, t.name, coalesce(t.hours, 0) as hours
from generate_series('2018-05-01'::date, '2018-05-10'::date, interval '1 day') g(dte) left join
     t 
     on g.dte = t.date;

对于多个用户，需要为所有用户生成所有行，然后left join:

select g.dte, n.name, coalesce(t.hours, 0) as hours
from generate_series('2018-05-01'::date, '2018-05-10'::date, interval '1 day'
                    ) g(dte) cross join
     (select distinct name from t) n left join
     t 
     on g.dte = t.date and n.name = t.name;