跨多个列搜索多个单词

Question

有一个简单的表:

CREATE TABLE contact (
id serial NOT NULL,
"firstName" varchar NOT NULL,
"middleName" varchar NULL,
"lastName" varchar NOT NULL
);

如何构建一个查询来搜索所有字段与部分支持?

假设我有以下条目:

firstName middleName lastName
John      Mark       Smith
Barbara   Alice      Johnson
John      Bob        Johson

当我搜索

'joh smi'或'smi joh'我想检索第一个记录

但是当我搜索'joh joh'时，我只想检索最后一条记录，而不是前两条记录。

Answer 1

如果您使用两个单词进行搜索(一个名字和一个姓氏，但您不知道哪个是哪个)，您可以创建如下函数:

CREATE FUNCTION twoWordsSearchForContacts(words text) RETURNS SETOF contact AS
$$
DECLARE
aWords text[] := string_to_array(words, ' ');
word1 text := aWords[1];
word2 text := aWords[2];
BEGIN
RETURN QUERY SELECT * 
FROM contact 
WHERE ("lastName" ILIKE '%' || word1 || '%' AND "firstName" || ' ' || "middleName" ILIKE '%' || word2 || '%')
OR ("lastName" ILIKE '%' || word2 || '%' AND "firstName" || ' ' || "middleName" ILIKE '%' || word1 || '%');
END;
$$ LANGUAGE plpgsql;

使用函数:

SELECT * FROM twoWordsSearchForContacts('smi joh');

您也可以在查询中直接拆分单词，而不使用函数。