PGSQL

本文介绍了PGSQL-在复杂条件下联接两个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在PostgreSQL上进行数据库迁移时，我陷入了困境，需要您的帮助.

I got stuck during database migration on PostgreSQL and need your help.

我有两个表需要联接:drzewa_mateczne.migracja(我需要迁移的数据)和ibl_as.t_adres_lesny(我需要与migracja联接的字典).

I have two tables that I need to join: drzewa_mateczne.migracja (data I need to migrate) and ibl_as.t_adres_lesny (dictionary I need to join with migracja).

我需要在replace(drzewa_mateczne.migracja.adresy_lesne，''，))= replace(ibl_as.t_adres_lesny.adres，''，))上加入他们.但是我的数据不是很规则，因此我希望在与字典的第一个良好匹配时将其加入.

I need to join them on replace(drzewa_mateczne.migracja.adresy_lesne, ' ', '') = replace(ibl_as.t_adres_lesny.adres, ' ', ''). However my data is not very regular, so I want to join it on first good match with the dictionary.

我创建了以下查询:

select count(*) from drzewa_mateczne.migracja a where length(a.adresy_lesne) > 0 and replace(a.adresy_lesne, ' ', '') = (select substr(replace(al.adres, ' ', ''), 1, length(replace(a.adresy_lesne, ' ', ''))) from ibl_as.t_adres_lesny al limit 1)

查询不返回任何行. 如果没有

The query doesn't return any rows. It does successfully join empty rows if ran without

length(a.adresy_lesne) > 0

以下两个查询返回行(按预期):

The two following queries return rows (as expected):

select replace(adres, ' ', '') from ibl_as.t_adres_lesny where substr(replace(adres, ' ', ''), 1, 16) = '16-15-1-13-180-c' limit 1 select replace(adresy_lesne, ' ', ''), length(replace(adresy_lesne, ' ', '')) from drzewa_mateczne.migracja where replace(adresy_lesne, ' ', '') = '16-15-1-13-180-c'

我怀疑子查询中的"where"子句中的子查询可能存在问题.如果你们可以帮助我解决这个问题，或者至少将我指出正确的方向，那我将非常感激.

I'm suspecting that there might be a problem in sub-query inside the 'where' clause in my query. If you guys could help me resolve this issue, or at least point me in the right direction, I'd be very greatful.

预先感谢， 扬

推荐答案

您可以在很大程度上简化为:

You can largely simplify to:

SELECT count(*) FROM drzewa_mateczne.migracja a WHERE a.adresy_lesne <> '' AND EXISTS ( SELECT 1 FROM ibl_as.t_adres_lesny al WHERE replace(al.adres, ' ', '') LIKE (replace(a.adresy_lesne, ' ', '') || '%') )

• a.adresy_lesne <> ''与length(a.adresy_lesne) > 0相同，只是速度更快.
• 用 EXISTS 半联接(每行仅获得一个匹配项).
• 用简单的LIKE表达式替换复杂的字符串结构.
• a.adresy_lesne <> '' does the same as length(a.adresy_lesne) > 0, just faster.
• Replace the correlated subquery with an EXISTS semi-join (to get only one match per row).
• Replace the complex string construction with a simple LIKE expression.

这些相关答案中有关模式匹配和索引支持的更多信息: PostgreSQL LIKE查询性能差异 在Postgres中，LIKE和〜之间的区别 加快通配符文本查找

More information on pattern matching and index support in these related answers: PostgreSQL LIKE query performance variations Difference between LIKE and ~ in Postgres speeding up wildcard text lookups