查询带Datatype位的Counting列

Question

我有一个列名检查，有一个数据类型位( true或False)，我想做的是计算列中有多少false和true。

SELECT 
COUNT(FeatureState) AS tot_true, 
COUNT(*)-COUNT(FeatureState) AS tot_false
FROM productDetail
 WHERE FeatureState= 1

这是可行的

Answer 1

试试这个，应该行得通：

SELECT 
    COUNT(field) AS tot_true, 
    COUNT(*)-COUNT(field) AS tot_false
FROM table
WHERE field=1

Answer 2

你觉得怎么样？

SELECT 
  COUNT(CASE fld WHEN 0 THEN 1 ELSE NULL END) AS ZEROS,
  COUNT(CASE fld WHEN 1 THEN 1 ELSE NULL END) AS ONES
FROM
  T

--alternative

SELECT 
  SUM(CASE fld WHEN 0 THEN 1 ELSE 0 END) AS ZEROS,
  SUM(CASE fld WHEN 1 THEN 1 ELSE 0 END) AS ONES
FROM
  T

Answer 3

我已经使用符号将bit更改为int

SELECT 
    SUM(SIGN(field)) AS tot_true, 
    SUM(1-SIGN(field)) AS tot_false
FROM table

或者使用COUNT忽略NULL的事实

SELECT 
    COUNT(NULLIF(field, 0)) AS tot_true, 
    COUNT(NULLIF(field, 1)) AS tot_false
FROM table

如果您需要其他值MAX(SomeOtherField)，其中最高值来自在其他解决方案中过滤掉的"false“行，则可以使用此方法