postgresql - sql : how to select a row with a true value from a column of boolean values after the HAVING clause

Question

HI 有 3 个产品表，每个表有 3 列，即客户名称、 bool 选择退出和黑名单。在 Having 子句之后，每个客户名称将有 3 行(假设他拥有所有 3 种产品)。

如果任何 bool 列包含 true，我如何输出 true。我通过使用下面的 cast 操作想通了，但认为应该有一个更优雅的解决方案。

SELECT customer_name,
       cast(int4(sum(cast(optout     As int4))) As Boolean) As optout, 
       cast(int4(sum(cast(blacklist  As int4))) As Boolean) As blacklist
FROM
(SELECT * FROM product1
UNION SELECT * FROM product2
UNION SELECT * FROM product3) AS temp1
GROUP BY customer_name, optout, blacklist
HAVING optout=true or blacklist=true;

Answer 1

试试 bool_or聚合函数，听起来正是您要找的:

SELECT customer_name,
       bool_or(optout)    As optout,
       bool_or(blacklist) As blacklist
FROM
(SELECT * FROM product1
UNION SELECT * FROM product2
UNION SELECT * FROM product3) AS temp1
GROUP BY customer_name, optout, blacklist
HAVING optout=true or blacklist=true;