SQL 如何聚合布尔列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37947668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to aggregate boolean column
提问by chrisblo
How can I aggregate some tuples like this
我怎样才能聚合一些这样的元组
COL_1 | COL_2 | COL_3 | COL_4
val | T | F | F
val | F | T | F
with the OR function and return the following table?
使用 OR 函数并返回下表？
COL_1 | COL_2 | COL_3 | COL_4
val | T | T | F
回答by jarlh
Simply do a GROUP BY, use MAX()to return T if available, else F.
只需执行 a GROUP BY，MAX()如果可用，则使用返回 T，否则返回 F。
select col_1, max(col_2), max(col_3), max(col_4)
from tablename
group by col_1
回答by Cito
Just as a side note (doesn't work with Oracle): In PostgreSQL, you would do this:
正如旁注（不适用于 Oracle）：在 PostgreSQL 中，您可以这样做：
select col_1, bool_or(col_2), bool_or(col_3), bool_or(col_4)
from tablename group by col_1 order by col_1
回答by Olivier Jacot-Descombes
If COL_2to COL_4are text columns (char, varchar, varchar2, nvarchar, nvarchar2) containing 'T'or 'F', then you can just take the MAXof them, since 'T' > 'F', i.e. 'T'comes after 'F'in lexical order.
如果COL_2toCOL_4是包含or 的文本列（char, varchar, varchar2, nvarchar, nvarchar2），那么您可以只取其中的 ，因为, ie按词法顺序排在后面。'T''F'MAX'T' > 'F''T''F'
SELECT COL_1, MAX(COL_2) AS COL_2, MAX(COL_3) AS COL_3, MAX(COL_4) AS COL_4
FROM table
GROUP BY COL_1
Explanation: The locical operation OR returns TRUE, if at least one of the operands is TRUE. MAX() returns "T"if at least one value is "T"and otherwise "F".
说明：如果至少有一个操作数为 TRUE，则局部运算 OR 返回 TRUE。"T"如果至少有一个值，则MAX() 返回"T"，否则返回"F"。
Note: If the Boolean columns were declared as
注意：如果布尔列被声明为
COL_x NUMBER(1) DEFAULT 0 NOT NULL
or any other numeric type then I would take MAX(ABS(col_x)), since a negative value counts as TRUE as well. (If you have an Access frontend with a ComboBox attached to a Boolean, it yields the values 0or -1.)
或任何其他数字类型，那么我会采用MAX(ABS(col_x))，因为负值也算作 TRUE。（如果您的 Access 前端带有附加到布尔值的 ComboBox，它会生成值0或-1。）
回答by Adres Antyspamowy
SELECT col1 = MAX(CONVERT(tinyint, ISNULL(col1,0))) ...
Explanation: (S)He's first taking the boolean in col1, or false if null. That gets converted into a TINYINT data type instead of BIT, which may then use the MAX() aggregate function which effectively says "if any are true, return true" in your GROUP BY.
解释：(S)他首先在 col1 中取布尔值，如果为 null，则为 false。这将转换为 TINYINT 数据类型而不是 BIT，然后它可以使用 MAX() 聚合函数，该函数在您的 GROUP BY 中有效地表示“如果有任何为真，则返回真”。
回答by Heffalumpene
I suggest you try this:
我建议你试试这个：
declare @tempTable table(bitval bit)
insert into @tempTable values(1)
insert into @tempTable values(1)
insert into @tempTable values(0)
insert into @tempTable values(1)
select min(cast(bitval as int)) as AndAggregate from @tempTable
select max(cast(bitval as int)) as ORAggregate from @tempTable
