与文本解析和排序条件的一对多关系

Question

我正在搜索一个sql查询，在两个表之间建立一个新的n:1关系，并有更多的条件。Table_A在名为"id_a"的列中具有固定数量的具有唯一id (varchar)的多边形。Table_B存储了这些多边形的邻近点以及它们到特定目标的距离。table_B中的每个点都有一个列poly_ids"它将相邻的多边形以逗号分隔的ID存储为一个唯一的ID，称为"id_b"并且距离属性只是一个值(double)。

我如何为每个多边形找到一个具有最小距离属性的相邻点?

我已经尝试了这个和其他一些事情，但没有成功:

CREATE TABLE match AS 
(SELECT id_a FROM table_A 
(DECLARE my_id (varchar 255) := id_a)) 
UNION
(SELECT id_b FROM table_B 
WHERE poly_ids LIKE '%my_id%'
AND 
table_B.distance <= MIN(table_B.distance);

我不知道这是否会找到一个匹配，但这是两个表的结构:

Polygon-Table:

id_a;row
DENWLI0540200151;"1"
DENWLI0540201042;"2"
DENWLI0539203285;"3"
DENWLI0540200526;"4"
DENWLI0539200707;"5"
DENWLI0540200150;"6"
DENWLI0540200529;"7"
DENWLI0540211664;"8"
DENWLI0539203286;"9"
DENWLI0539200119;"10"
DENWLI0539202148;"11"
DENWLI0539200482;"12"
DENWLI0540200644;"13"
DENWLI0540202807;"14"
DENWLI0539202146;"15"
DENWLI0539200820;"16"
DENWLI0539200487;"17"
DENWLI0540200386;"18"
DENWLI0539200708;"19"
DENWLI0539200233;"20"
DENWLI0539203289;"21"
DENWLI0539202903;"22"
DENWLI0540200274;"23"
DENWLI0539202902;"24"
DENWLI0539201377;"25"

邻居Points_Table:

Distance_m;id_b;poly_ids
458.381978;"1";"DENWLI0540200151,DENWLI0539202146"
388.181258;"2";DENWLI0539203285
347.828999;"3";"DENWLI0539203285,DENWLI0539200630"
347.828999;"4";DENWLI0539203285
1121.671746;"5";DENWLI0540200526
147.780569;"6";DENWLI0540200526
1212.098782;"7";DENWLI0540200526
534.862435;"8";"DENWLI0540200526,DENWLI0540200300,DENWLI0540201374"
169.452134;"9";"DENWLI0540200526,DENWLI0540200090"
76.982818;"10";"DENWLI0540200526,DENWLI0540202246"
1040.426788;"11";"DENWLI0540200526,DENWLI0540202010"
55.631933;"12";"DENWLI0540200526,DENWLI0540202246"
66.441046;"13";"DENWLI0539200707,DENWLI0539202093,DENWLI0539200581"
192.089280;"14";"DENWLI0539200707,DENWLI0539202121"
330.531804;"15";"DENWLI0539200707,DENWLI0539200767"
337.169176;"16";"DENWLI0540200150,DENWLI0540200538"
729.729942;"17";DENWLI0540200150
136.210113;"18";DENWLI0540200529
498.361421;"19";DENWLI0540200529
428.060984;"20";DENWLI0540200529
170.279163;"21";DENWLI0540200529

结果应该是这样的，id_b是唯一一个具有最小距离的匹配对象:

id_a; id_b; Distance_m
DENWLI0540200151; "1";  458.381978
DENWLI0540201042; null; null
DENWLI0539203285; "2";  347.828999
DENWLI0540200526; "9";  169.452134
DENWLI0539200707; "13"; 66.441046
DENWLI0540200150; "16"; 337.169176
DENWLI0540200529; "18"; 136.210113

所以结果列表正好和table_a一样长。或者:用table_b (id_b和distance_m)的最佳匹配值展开table_a。如果没有匹配到id_a，则id_b和distance的值为id_a; null; null。如果table_b中有多个匹配对象具有完全相同的最小距离，则可以使用其中任何一个。

Answer 1

(作为注释会很乱)

我不知道你想要得到什么结果，但对于一个简单的你可以这样做:

select *
from tableA a
left join (
select regexp_split_to_table(poly_ids, ',') polyId, id_b, distance_m from tableB) b on b.polyId = a.id_a;

编辑:获取每个id_a的最小值(只有那些有distance_m的):

select id_a, min(b.distance_m)
from tableA a
inner join (
select regexp_split_to_table(poly_ids, ',') polyId, id_b, distance_m from tableB) b on b.polyId = a.id_a
group by id_a;