时间戳

首页 > 解决方案 > 如何根据开始和结束日期复制记录

sql - 如何根据开始和结束日期复制记录

问题描述

我有一个 SQL 数据库中的记录，这些记录有一个startDate并且endDate我需要扩展。

| userName    | startDate  | endDate    | weekDay |  
| :---------: | :--------: | :--------: | :-----: |  
| Test User 1 | 2011-03-30 | 2011-04-05 | 1       |  
| Test User 2 | 2016-10-05 | 2016-10-07 | 5       |  
| Test User 3 | 2018-05-22 | 2018-05-26 | 4       |

在上表中，每条记录都包含涵盖多个日期的信息。我需要的是每个用户每个日期一个记录。我正在寻找的一个例子：

| userName    | startDate  | weekDay    |
| :---------: | :--------: | :--------: |
| Test User 1 | 2011-03-30 | 1          |
| Test User 1 | 2011-03-31 | 1          |
| Test User 1 | 2011-04-01 | 1          |
| Test User 1 | 2011-04-02 | 1          |
| Test User 1 | 2011-04-03 | 1          |
| Test User 1 | 2011-04-04 | 1          |
| Test User 1 | 2011-04-05 | 1          |
| Test User 2 | 2016-10-05 | 5          |
| Test User 2 | 2016-10-06 | 5          |
| Test User 2 | 2016-10-07 | 5          |
| Test User 3 | 2018-05-22 | 4          |
| Test User 3 | 2018-05-23 | 4          |
| Test User 3 | 2018-05-24 | 4          |
| Test User 3 | 2018-05-25 | 4          |
| Test User 3 | 2018-05-26 | 4          |

这个答案让我更近了一步，指定了如何在 SQL 中生成日期序列。如何根据 SQL 中的开始和结束日期复制表格记录？

作为说明，我需要这个解决方案才能在 MSSQL 和 PostgreSQL 中工作。

标签： sqlsql-serverpostgresqldate-range

解决方案

您可以在 SQL Server 和 Postgres 中使用递归 CTE，但语法略有不同。而且，在 Postgres 中有一个更简单的方法。因此，在 SQL Server 中，您可以执行以下操作：

with cte as (
      select username, startdate, weekday, enddate
      from t
      union all
      select username, dateadd(day, 1, startdate) weekday, enddate
      from cte
      where startdate < enddate
     )
select username, startdate, weekday
from cte
order by username, startdate;

您可以调整日期算术并recursive为 Postgres 添加关键字。

Postgres 中更简单的方法是横向连接：

select t.username, g.startdate, t.weekday
from t, lateral
     generate_series(start_date, end_date, interval '1 day') g(startdate);

如果您需要相同的代码在两者中工作，则需要生成一个数字表。这是一种（不愉快的）方法：

with digits as (
      select v.n
      from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(n)
     ),
     n as (
      select d1.n * 100 + d2.n * 10 + d3.n as n
      from digits d1 cross join digits d2 cross join digits d3
     )
select t.username, t.startdate + n.n, t.weekday
from t join
     n 
     on t.startdate + n.n <= t.enddate;

请注意，要使其工作startdate，需要datetime在 SQL Server 中，但date在 Postgres 中。

推荐阅读