我试图连接两个不相关的表，两个表都有一个共同的列，第三个表的外键，如果假设第一个表是通知表，第二个表是照片表。设备根据几个标准及时拍照，如果有任何移动，也根据通知。需要同时选择通知和通知后的一张照片。
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;
请建议。我知道我的解决方案是不对的
您可以使用不同的on来获得单个通知+照片。问题是Notification中的id1不是唯一的(打印错误?)注意，对age函数进行排序会给出Notification和Photo之间的最小时间。(见演示)
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);
try this:
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