postgresql - Postgres 子查询限制

Question

我有一个查询尝试使用姓名、电话和电子邮件值查找相似的联系人。

select *, ranking function(...) as score from
( select * from contacts where 
    'Dave' <% coalesce(contact_name1, '') 
  UNION
  select * from contacts_index where 
    '9782736623' <% coalesce(phone1, '')   
  UNION
  select * from contacts where 
    'dave_79@gmail.com' <% coalesce(email1, '')  
  UNION 
  select * from contacts where 
    '26 Ashley Gardens' <% coalesce(address1, '')                          
)  AS sub
limit 1000 order by score desc;

我使用限制 1000 来防止在查询非常常见的名称(并且电子邮件、电话、地址为空)的情况下查询缓慢。这导致大量记录满足 where 子句，需要进行排名。

但是，我想将此限制分配给 4 个子查询，否则 name 子查询可能会用完所有 1000 个限制，并且具有相似电子邮件、电话或地址的记录将无法通过。

不幸的是，limit 似乎不能用在子查询中。有谁知道我怎样才能做到这一点

Answer 1

这是您实际上需要将联合的子查询放在括号之间的罕见情况之一:

select *, ranking function(...) as score 
from
( 
  (
    select * 
    from contacts where  'Dave' <% coalesce(contact_name1, '') 
    limit 250
  )
  UNION
  (
    select * 
    from contacts_index 
    where '9782736623' <% coalesce(phone1, '')   
    limit 250
  )
  UNION
  (
    select * 
    from contacts 
    where 'dave_79@gmail.com' <% coalesce(email1, '')  
    limit 250
  )
  UNION 
  (
    select * 
    from contacts 
    where '26 Ashley Gardens' <% coalesce(address1, '')
    limit 250
)  AS sub
order by score desc;