postgresql - 如何创建一个具有与另一个表相关的日期序列的虚拟表？

Question

我有两个表。

一张表有员工积分，另一张表有员工的数据。

您可以在下面看到两个表格:

表:Horas_trabalho

315           |          09:45:00           |          2015-08-15
315           |          05:55:00           |          2015-08-17
315           |          05:55:00           |          2015-08-15
315           |          10:50:00           |          2015-08-13
315           |          05:55:00           |          2015-08-14
315           |          05:55:00           |          2015-08-13
315           |          09:44:00           |          2015-08-13
315           |          05:55:00           |          2015-08-01
315           |          09:47:00           |          2015-08-04
315           |          09:47:00           |          2015-08-04
315           |          14:27:00           |          2015-08-01
315           |          15:50:00           |          2015-08-04

表:函数

315           |          "EISZUPERIO DA SILVA ROCHA"           |          NULL           |          5793           |          10829720267           |          2000-08-19           |          NULL           |          9           |          2

随着查询，下面...

SELECT f.matricula, f.nome, h.data, h.hora from funcionario f
LEFT JOIN horas_trabalho h ON h.funcionario_id = f.id
WHERE f.id = 315
ORDER BY h.data;

我有这样的结果:

5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-01           |          05:55:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-01           |          09:44:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-01           |          14:27:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-01           |          10:49:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-04           |          15:50:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-04           |          10:53:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-04           |          09:47:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-04           |          09:47:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-04           |          05:55:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-05           |          05:55:00

我也想获得员工当天没有的积分(数据库中不存在)。像这样的东西:

5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-03           |          NULL
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-04           |          15:50:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-04           |          10:53:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-04           |          09:47:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-04           |          09:47:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-04           |          05:55:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-05           |          05:55:00
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-06           |          NULL
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-07           |          NULL
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-08           |          NULL
5793           |          "EISZUPERIO DA SILVA ROCHA"           |          2015-08-09           |          NULL

详情:当时间值为NULL时，日期也为空。但我想显示员工在该日期没有记录的时间。我仍然想不出该怎么做。

Answer 1

您可以使用计数表创建日期序列，计数表是一个包含单行日期的表。下面是创建这个包含 10000 天的表的基本语法，从 1/1/1990 开始计数-

select dateadd(dd,(row_number() over (order by (select null))),'1/1/1990') n
from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)

从这里开始，您只需要从理货表左连接到理货表生成的日期的主查询。这将为每个日期创建一行，无论表中是否有数据:Horas_trabalho(表中的空白行:Horas_trabalho 将显示为 NULL)。

select *
from
(
    select dateadd(dd,(row_number() over (order by (select null))),'1/1/1990') n
    from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
    cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
)
left join 
(
    SELECT f.matricula, f.nome, h.data, h.hora 
    from funcionario f
    LEFT JOIN horas_trabalho h ON h.funcionario_id = f.id
    WHERE f.id = 315
    ORDER BY h.data
) d
on t.n = d.hora