开发者

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

HI have 3 product tables, each with 3 columns namely customer name, and boolean optout and blacklist. After the Having clause, there will be 3 rows for each customer name (assuming he has all 3 products).

How do I output a true if any of the boolean columns contains a true. I figured out by using the cast operation below, but think there should be a more elegant solution.

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;


Try the bool_or aggregate function, sounds like exactly what you're looking for:

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;


If I have understood the question correctly I think you just need a CASE statement in the SELECT e.g.

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜