根据日期从单行生成多行 [英] Generating multiple rows from a single row based on dates

问题描述

我有一个数据库表，其中包含开始日期和月数。如何根据月数将其转换为多行？

I have a database table with a start date and a number of months. How can I transform that into multiple rows based on the number of months?

我要转换此

此：

推荐答案

我们可以在此处尝试使用日历表，其中包括可能出现在预期输出中的所有月份开始日期：

We can try using a calendar table here, which includes all possible start of month dates which might appear in the expected output:

with calendar as (
    select '2017-09-01'::date as dt union all
    select '2017-10-01'::date union all
    select '2017-11-01'::date union all
    select '2017-12-01'::date union all
    select '2018-01-01'::date union all
    select '2018-02-01'::date union all
    select '2018-03-01'::date union all
    select '2018-04-01'::date union all
    select '2018-05-01'::date union all
    select '2018-06-01'::date union all
    select '2018-07-01'::date union all
    select '2018-08-01'::date
)

select
    t.id as subscription_id,
    c.dt,
    t.amount_monthly
from calendar c
inner join your_table t
    on c.dt >= t.start_date and
       c.dt < t.start_date + (t.month_count::text || ' month')::interval
order by
    t.id,
    c.dt;

演示

这篇关于根据日期从单行生成多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！