我有兩張桌子
T1 :
| ID | 參考 | 地位 | 事件時間戳 |
|---|---|---|---|
| 1 | 花朵 | 已派遣 | 2021-12-10 |
| 2 | 花朵 | 發表 | 2021-12-11 |
和T2：
| ID | 參考 | 評論 | 事件時間戳 |
|---|---|---|---|
| 1 | 花朵 | 做得好 | 2021-12-12 |
| 2 | 花朵 | 繼續 | 2021-12-13 |
| 3 | 鍋 | 隨機的 | 2021-12-13 |
我試圖通過查詢Flowers參考獲得的表是（使用 Postgres）
| t1_ID | t2_ID | 參考 | 評論 | 地位 | t1_Event 時間戳 | t2_Event 時間戳 |
|---|---|---|---|---|---|---|
| 1 | 空值 | 花朵 | 空值 | 已派遣 | 2021-12-10 | 空值 |
| 2 | 空值 | 花朵 | 空值 | 發表 | 2021-12-11 | 空值 |
| 空值 | 1 | 花朵 | 做得好 | 發表 | 2021-12-11 | 2021-12-12 |
| 空值 | 2 | 花朵 | 繼續 | 發表 | 2021-12-11 | 2021-12-13 |
換句話說，我需要一個連接表來記錄兩個表之間的所有更新。
我嘗試了很多查詢，如LEFT JOIN、UNION 等，但所有嘗試都沒有成功。
你能建議我應該使用哪些 SQL 陳述句來獲得預期的結果嗎？
uj5u.com熱心網友回復：
您可以在最近的日期使用unionT1 的行join間和 T1 和 T2之間的行：
select "ID" as t1_id,
null as t2_id,
"Reference",
null as "Comments",
"Status",
"Event Timestamp" as "t1_Event Timestamp",
null as "t2_Event Timestamp"
from T1
union all
select T1."ID" as t1_id,
T2."ID" as t2_id,
T1."Reference",
"Comments",
"Status",
T1."Event Timestamp" as "t1_Event Timestamp",
T2."Event Timestamp" as "t2_Event Timestamp"
from T1 inner join T2
on T1."Event Timestamp" =
(select max("Event Timestamp") from T1 as t where t."Event Timestamp" < T2."Event Timestamp" and t."Reference" = T2."Reference")
and T1."Reference" = T2."Reference"
where T1."Reference" = 'Flowers'
小提琴
如果您真的希望在最后兩行中將 null 作為 t1_id ，您可以在第二個選擇中替換 T1."ID"為null：
select null as t1_id,
uj5u.com熱心網友回復：
使用 cte 和 union
with cte as(
select ID as t1_ID,
null as t2_ID,
Reference,
null as Comments,
Status,
Event_Timestamp as t1_Event_Timestamp,
null as t2_Event_Timestamp,
row_number() over(partition by Reference order by Reference) seq
from t1
)
select t1_id,t2_id,reference,comments,status,t1_event_timestamp,t2_event_timestamp
from cte
union all
select null as t1_ID,
t2.ID::varchar(10) as t2_ID,
t2.Reference,
Comments,
(select max(Status) from cte t3 where t3.seq = (select max(seq) from cte)) as status,
(select max(t1_Event_Timestamp) from cte t3 where t3.Reference = t2.Reference) as t1_Event_Timestamp,
t2.Event_Timestamp as t2_Event_Timestamp
from t1
left join t2 on t1.Reference = t2.Reference
group by t2.ID,t2.Reference,t2.Comments,t2.Event_Timestamp
db<>fiddle 中的演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/397719.html
標籤：sql PostgreSQL的
上一篇：SqlException“除以零”在orderby子句中
下一篇：如何顯示下一次計劃檢查？