PGSQL-在复杂条件下联接两个表 [英] PGSQL - Joining two tables on complicated condition

问题描述

在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

这篇关于PGSQL-在复杂条件下联接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！