我試圖加入兩個不相關的表，兩個表都有一個公共列，第三個表的外鍵，如果假設第一個表是通知表，第二個表是照片表。設備根據多個標準及時拍攝照片（如果有任何移動）以及通知。需要選擇兩個通知，并且在該通知之后只選擇一張照片。
Notification Table
----- ---------------------------- ---------- ----------------
| id1 | Notification timestamp | data1 | deviceID |
----- ---------------------------- ---------- ----------------
| 11 | 2020-10-26 22:31:33.602 | 2038.75 | 6 |
| 12 | 2020-10-26 22:31:34.001 | 2043.5 | 2 |
| 13 | 2020-10-26 22:51:47.178 | 1778.75 | 8 |
| 14 | 2020-10-26 23:12:07.761 | 2015.75 | 3 |
| 15 | 2020-10-26 23:12:09.611 | 2005.75 | 2 |
| 16 | 2020-10-26 23:12:09.122 | 1963.25 | 7 |
| 17 | 2020-10-26 23:12:11.930 | 694.75 | 2 |
----- ---------------------------- ---------- ----------------
Photo Table
----- --------------------------- ---------- ----------------
| id2 | Photo timestamp | data2 | deviceID |
----- --------------------------- ---------- ----------------
| 21 | 2020-10-26 22:31:34.016 | 5 | 2 |
| 22 | 2020-10-26 22:31:34.102 | 75 | 6 |
| 23 | 2020-10-26 22:31:34.022 | 20 | 3 |
| 24 | 2020-10-26 22:51:47.97 | 55 | 2 |
| 25 | 2020-10-26 22:51:47.975 | 63 | 7 |
| 26 | 2020-10-26 22:51:47.977 | 19 | 4 |
| 27 | 2020-10-26 22:51:47.978 | 77 | 8 |
| 28 | 2020-10-26 23:12:07.613 | 44 | 6 |
| 29 | 2020-10-26 23:12:08.61 | 11 | 3 |
| 30 | 2020-10-26 23:12:09.625 | 51 | 2 |
| 31 | 2020-10-26 23:12:09.628 | 63 | 7 |
| 32 | 2020-10-26 23:12:10.635 | 19 | 4 |
| 33 | 2020-10-26 23:12:11.635 | 77 | 8 |
| 34 | 2020-10-26 23:12:12.235 | 44 | 6 |
| 35 | 2020-10-26 23:12:12.435 | 11 | 3 |
| 36 | 2020-10-26 23:12:12.650 | 51 | 2 |
----- --------------------------- ---------- ----------------
Resultant Table
--------------------------- ---------- --------------------------- ---------- ----------------
| Notification timestamp | data1 | Photo timestamp | data | deviceID |
--------------------------- ---------- --------------------------- ---------- ----------------
| 2020-10-26 22:31:33.602 | 2038.75 | 2020-10-26 22:31:34.102 | 75 | 6 |
| 2020-10-26 22:31:34.001 | 2043.5 | 2020-10-26 22:31:34.016 | 5 | 2 |
| 2020-10-26 22:51:47.178 | 1778.75 | 2020-10-26 22:51:47.978 | 77 | 8 |
| 2020-10-26 23:12:07.761 | 2015.75 | 2020-10-26 23:12:08.61 | 11 | 3 |
| 2020-10-26 23:12:09.611 | 2005.75 | 2020-10-26 23:12:09.625 | 51 | 2 |
| 2020-10-26 23:12:09.122 | 1963.25 | 2020-10-26 23:12:09.628 | 63 | 7 |
| 2020-10-26 23:12:11.930 | 694.75 | 2020-10-26 23:12:12.650 | 51 | 2 |
--------------------------- ---------- --------------------------- ---------- ----------------
嘗試加入子查詢。雖然絕對不是正確的方法
select "notification".*, "filter_data".* FROM public.notification
left JOIN
( select "photo"."time", "photo"."data2", "photo"."deviceid", "notification"."id" as "eid", "notification"."time" as "etime"
from public."photo" inner join public."notification" on "notification"."deviceid" = "photo"."deviceid" where
"photo"."time" >= "notification"."time" order by "photo"."time" ASC
limit 1
)
filter_data on "filter_data"."did" = "notification"."deviceid"
where "notification"."time" > date '2021-10-28 01:37:20.305 00' - interval '7 days'
order by "notification"."device" ASC, "notification"."time" DESC limit 100;
請建議。我知道我的解決方案不正確
uj5u.com熱心網友回復：
嘗試這個 ：
WITH list AS
(
SELECT n.Notification, n.data1, n.deviceID, first_value(p.Photo) OVER (ORDER BY p.Photo ASC) AS Photo
, p.data2, p.deviceID
FROM Notification AS n
INNER JOIN Photo AS p
ON p.deviceID = n.deviceID
AND p.Photo >= n.Notification
GROUP BY n.Notification, n.data1, n.deviceID
)
SELECT l.Notification, l.data1, l.Photo, p.data2 AS data, l.deviceID
FROM list AS l
INNER JOIN Photo AS p
ON l.deviceID = p.deviceID
AND l.Photo = p.Photo
uj5u.com熱心網友回復：
您可以使用不同的 on來獲取單個通知 照片。問題是 Notification 中的 id1 不是唯一的（錯別字？）。請注意，排序年齡函式給出了通知和照片之間的最短時間。（見演示）
select distinct on (n.id1,n.deviceid)
n.ts "Notification Time"
, n.data1 "Notification Data"
, p.ts "Photo Time"
, p.data2 "Photo Data"
, n.deviceid "Device Id"
from notifications n
left join photos p
on ( p.deviceid = n.deviceid
and p.ts > n.ts
)
order by n.id1,n.deviceid,age(p.ts,n.ts);
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/341496.html
標籤：PostgreSQL