我在 PostgreSQL 表中有以下資料：
trial start_date end_date
1 20_12_2001 20_01_2005
預期輸出如下：
trial start_date end_date Date[(start_end_date)] marker_start_end
1 20_12_2001 20_01_2005 20_12_2001 start
1 20_12_2001 20_01_2005 20_01_2005 end
有沒有辦法在沒有連接的情況下計算額外的兩列 (Date[(start_end_date)], marker_start_end)，但是一個 CASE 運算式
uj5u.com熱心網友回復：
您可以使用橫向連接將兩列變成兩行：
select *
from the_table t
cross join lateral (
values (t.start_date, 'start'), (t.end_date, 'end')
) as x(start_end_date, marker);
解決方案可能會UNION ALL更快。
uj5u.com熱心網友回復：
UNION ALL
select trial, start_date, end_date, start_date as date, 'start' marker_start_end from table1
union all
select trial, start_date, end_date, end_date as date, 'end' marker_start_end from table1
UNNEST和CASE
select trial, start_date, end_date,
case when a.num = 1 then start_date else end_date end date,
case when a.num = 1 then 'start' else 'end' end marker_start_end from
(
select trial, start_date, end_date,
unnest(array[1,2]) num from table1
) a
隱藏JOIN（但仍加入）
select
trial,
start_date,
end_date,
case when a.num = 1 then start_date else end_date end date,
marker_start_end
from table1, (values(1,'start'),(2, 'end')) a(num,marker_start_end)
Db小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/497549.html
標籤：PostgreSQL
上一篇：客戶端后端VS后臺撰寫器