我需要创建一个查询,将一个二进制字段中True(1)和False(0)的数量分别累加到两个不同的列中。
我正在连接3个表格,并需要像这样:
属性 | 类 | 通过 | 失败
我将按照属性和类别进行分组。
我需要创建一个查询,将一个二进制字段中True(1)和False(0)的数量分别累加到两个不同的列中。
我正在连接3个表格,并需要像这样:
属性 | 类 | 通过 | 失败
我将按照属性和类别进行分组。
就像这样:
SUM(CASE WHEN ColumnName = 1 THEN 1 ELSE 0 END) AS Pass,
SUM(CASE WHEN ColumnName = 0 THEN 1 ELSE 0 END) AS Fail
这个有效(至少在 SQL 2008 中)
SELECT SUM(Passed + 0) PASS , SUM(1 - Passed) FAIL
在第一个求和中,我将0加到"Passed"中,这是一种将位转换为整数的简写方式,因为不能直接对位求和。
SUM(CAST(Passed as Integer)) PASS, SUM(1 - CAST(Passed AS Integer) FAIL
。 - karmakaze尝试:
declare @table table (columnName bit)
insert into @table values (1)
insert into @table values (1)
insert into @table values (1)
insert into @table values (1)
insert into @table values (1)
insert into @table values (0)
insert into @table values (0)
insert into @table values (0)
insert into @table values (0)
SELECT
SUM(CASE WHEN ColumnName = 1 THEN 1 ELSE 0 END) AS True1
, SUM(CASE WHEN ColumnName = 0 THEN 1 ELSE 0 END ) AS False0
from @Table
输出:
True1 False0
----------- -----------
5 4
(1 row(s) affected)
SELECT Attribute, Class
COUNT(CASE WHEN ColumnName = 1 THEN 1 END) Pass,
COUNT(CASE WHEN ColumnName = 0 THEN 1 END) Fail FROM YourTable
GROUP BY Attribute, Class
SELECT
Attribute,
Class,
SUM(CASE BitField WHEN 1 THEN 1 ELSE 0 END) AS [Pass],
SUM(CASE BitField WHEN 0 THEN 1 ELSE 0 END) AS [Fail]
FROM
Table
GROUP BY
Attribute,
Class
甚至还有一个选项:
SELECT
Attribute,
Class,
COUNT(BoolColumnName = 1 or NULL) Pass,
COUNT(BoolColumnName = 0 or NULL) Fail
FROM Table
GROUP BY Attribute, Class