所以我有2張桌子。在第一個表中，我需要將 ID 與最小和最大日期時間相關聯，我使用了
SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id
結果，我得到了大約 5k 的 ID 和 2 個時間戳。
我需要使用此資訊從 table2 中獲取具有 ID 的行，其中每個 ID 的日期時間分別在它們的 earlytime 和 latesttime 之間。或者只是 ID 和時間小于最新時間的行。
仍然無法弄清楚如何做到這一點。
想出了類似的東西
SELECT ID, source, amount, type, datetime
FROM table2
WHERE EXISTS (SELECT ID, min (datetime) as earlytime, max (datetime) as latetime
FROM table1
group by ID)
但我猜它只顯示了與 table1 中的 ID 匹配的任何行
任何人都可以幫助我嗎？
uj5u.com熱心網友回復：
也許您可以加入并獲得如下結果
select A.*
from table2 A
join
(
SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id
)B
on A.id=B.id and
B.timecol between earlytime and latesttime
uj5u.com熱心網友回復：
您可以使用 CTE 或子查詢的組合（無論您喜歡哪個）并BETWEEN()實作您的預??期輸出
with cte as (
select
id
,min(datetime) as earlytime
,max(datetime) as latesttime
from table1
group by id
)
select
c.id
,c.earlytime
,c.latesttime
,t2.* /*Table2 columns*/
from cte as c
inner join table2 as t2 ON c.id = t2.id
and t2.datetime between c.earlytime and c.latesttime
uj5u.com熱心網友回復：
SELECT T2.ID,T2.SOURCE,T2.AMOUNT,T2.TYPE,T2.DATETIME
FROM TABLE2 AS T2
JOIN
(
SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id
)X ON T2.ID=X.ID AND T2.DATETIME BETWEEN X.earlytime AND X.latesttime
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/463044.html
標籤：sql PostgreSQL
上一篇：選擇沒有分組依據的列的出現順序