根据日期从单行生成多行 [英] 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屋!
查看全文