postgresql - 选择不符合所有条件的 'where' 行

Question

我需要创建一个语句来选择符合 3 个或更多条件的所有行。这个语句将被一个高性能的 JS 函数使用。一条记录有一些信息:foo, bar, baz, qux and fum .我想到了 2 种方法来做到这一点，我选择了我认为最好的一种，但也许还有更好的？

第一个(我认为最好的)

1. 数据库运行一条语句，其中 where 子句匹配 3 个或更多条件

   I mean that for example the DB must returns all the rows where 3 fields or more match. Below there is an example of the query, is there a way do it ? I think this one is pretty ugly... And if I need to add some informations...

2. 如果超过 1 条记录，则 JS 脚本确定好的记录(遍历结果)

查询示例:

select * from MyTable
    where (foo='foo1' and bar='bar1' and baz='baz1')
    or    (foo='foo1' and bar='bar1' and qux='qux1')
    or    (foo='foo1' and bar='bar1' and fum='fum1')
    or    (bar='bar1' and baz='baz1' and qux='qux1')
    or    (bar='bar1' and baz='baz1' and fum='fum1')
    [other or for 3 conditions match ...]
    or    (foo='foo1' and bar='bar1' and baz='baz1' and qux='qux1')
    or    (foo='foo1' and bar='bar1' and baz='baz1' and fum='fum1')
    or    (foo='foo1' and bar='bar1' and qux='qux1' and fum='fum1')
    [other or for 4 conditions match ...]
    or    (foo='foo1' and bar='bar1' and baz='baz1' and qux='qux1' and fum='fum1') /* 5 conditions match */

另一个(我认为肯定是最糟糕的):

1. DB 返回在一个条件下匹配的所有行
2. 然后 JS 脚本通过遍历所有结果来确定好的

查询示例:

select * from MyTable
    where foo='foo1' or bar='baz1' or baz='baz1' or qux='qux1'

您是否同意第一个性能最好？如果是，是否有更好的查询？

Answer 1

demo:db<>fiddle

SELECT
    t.*
FROM mytable t
WHERE     
    (foo IS NOT DISTINCT FROM 'foo1')::int + 
    (bar IS NOT DISTINCT FROM 'bar1')::int + 
    (baz IS NOT DISTINCT FROM 'baz1')::int + 
    (qux IS NOT DISTINCT FROM 'qux1')::int + 
    (fum IS NOT DISTINCT FROM 'fum1')::int >= 3

IS NOT DISTINCT FROM 检查相等性并考虑 NULL