如何將不存在的組添加到存在于不同日期范圍內的案例。
資料：
ID GROUP_ID DATE_FROM DATE_TO DATE_NEW_FROM DATE_NEW_TO CASE_ID_GROUP NEW_ID
1 1 2019-10-05 2019-11-02 2019-10-05 2019-11-02 AAX2 AAX2
1 null null null 2019-10-22 2019-10-26 null AAX3
1 null null null 2019-10-24 2019-10-29 null AAX4
1 2 2018-05-05 2018-05-10 2018-05-05 2018-05-10 CCA2 CCA2
1 3 2020-05-20 2020-06-15 2020-05-20 2020-06-15 BBA1 BBA1
1 null null null 2020-05-25 2020-05-29 null BBA2
2 and so on...
期望的輸出：
ID GROUP_ID DATE_NEW_FROM DATE_NEW_TO NEW_ID
1 1 2019-10-05 2019-11-02 AAX2
1 1 2019-10-22 2019-10-26 AAX3
1 1 2019-10-24 2019-10-29 AAX4
1 2 2018-05-05 2018-05-10 CCA2
1 3 2020-05-20 2020-06-15 BBA1
1 3 2020-05-25 2020-05-29 BBA2
2 and so on...
當DATE_NEW_FROM BETWEEN DATE_FROM 和 DATE_TO THEN GROUP_ID 時，但我的 date_from 和 date_to 為空。所以我不知道如何將它添加到所需的 GROUP_ID。
uj5u.com熱心網友回復：
您可以將其視為帶有聯合的兩個步驟或左自聯接。兩者都涉及加入以尋找匹配的日期范圍：
with notmatched as (select * from T where GROUP_ID is null),
matched as (select * from T where GROUP_ID is not null)
select
ID, GROUP_ID, DATE_NEW_FROM, DATE_NEW_TO
from matched
union all
select
n.ID, m.GROUP_ID, n.DATE_NEW_FROM, n.DATE_NEW_TO
from notmatched n inner join matched m
on m.ID = n.ID
and n.DATE_NEW_FROM between m.DATE_FROM and m.DATE_TO
and n.DATE_NEW_TO between m.DATE_FROM and m.DATE_TO
order by id, group_id;
交替：
select
n.ID,
coalesce(m.GROUP_ID, n.GROUP_ID) as GROUP_ID,
coalesce(m.DATE_NEW_FROM, n.DATE_FROM) as DATE_NEW_FROM,
coalesce(n.DATE_NEW_TO, n.DATE_TO) as DATE_NEW_TO
from T n /* not matched */ left outer join T m /* matched */
on m.ID = n.ID and n.GROUP_ID is null
and n.DATE_NEW_FROM between m.DATE_FROM and m.DATE_TO
and n.DATE_NEW_TO between m.DATE_FROM and m.DATE_TO
order by id, group_id;
https://dbfiddle.uk/?rdbms=postgres_10&fiddle=373f0d4f9ef5f630c3799000a96bd1ed
uj5u.com熱心網友回復：
您可以使用JOIN與條件之間創建一個資料集，表中右側包括實時資料（與空GROUP_ID），并在表的左側，包括不為空GROUP_ID資料然后檢查右側日期左手應該存在然后我們將列與coalesce
演示
select
t2.ID,
coalesce(t2.GROUP_ID, t1.GROUP_ID),
t2.DATE_NEW_FROM,
t2.DATE_NEW_TO,
t2.NEW_ID
FROM
test t1
join test t2 on
t2.DATE_NEW_FROM between t1.DATE_FROM and t1.DATE_TO
WHERE
t1.GROUP_ID notnull
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/364933.html
標籤：sql PostgreSQL的
上一篇：是否可以加入多行