sql：如何从 HAVING 子句后的布尔值列中选择具有真值的行

Question

HI 有 3 个产品表，每个表有 3 列，即客户名称、布尔选择退出和黑名单。在 Have 子句之后，每个客户名称将有 3 行（假设他拥有全部 3 种产品）。

如果任何布尔列包含 true，如何输出 true。我通过使用下面的强制转换操作弄清楚了，但认为应该有一个更优雅的解决方案。

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;

Answer 2

如果我正确理解了这个问题，我认为你只需要在 SELECT 中使用 CASE 语句，例如

CASE
WHEN blackLIST = TRUE OR optout = TRUE THEN 1
ELSE 0
END