如何匹配至少2个条件？

Question

我有一个包含五个布尔列的表。

我该如何构造一个返回至少有2列为真的行的查询？

Answer 1

铸布尔类型的整数（0=false，1=true），并检查他们的总和：

select * 
from my_table 
where a::int + b::int + c::int + d::int + e::int >= 2; 

Answer 2

很长的路要走：

SELECT * from t where c1 and c2 or c1 and c3 or c1 and c4 or c1 and c5 
or c2 and c3 or c2 and c4 or c2 and c5 or c3 and c4 or c3 and c5 or c4 and c5; 

Answer 3

接受的答案只能假设所有列被定义NOT NULL，没有被指定。为了使其与NULL工作，太：

SELECT * 
FROM tbl 
WHERE (a IS TRUE)::int 
    + (b IS TRUE)::int 
    + (c IS TRUE)::int 
    + (d IS TRUE)::int 
    + (e IS TRUE)::int > 1; 

或者：

SELECT * 
FROM tbl 
WHERE COALESCE(a::int, 0) 
    + COALESCE(b::int, 0) 
    + COALESCE(c::int, 0) 
    + COALESCE(d::int, 0) 
    + COALESCE(e::int, 0) > 1;