将 MIN 聚合函数应用于 BIT 字段

我想写下面的问题:

SELECT   ..., MIN(SomeBitField), ...
FROM     ...
WHERE    ...
GROUP BY ...

问题是，当我要计算位字段的最小值时，SQLServer 不喜欢它返回错误 Operand data type bit is invalid for min operator。

我可以使用以下变通方法:

SELECT   ..., CAST(MIN(CAST(SomeBitField AS INT)) AS BIT), ...
FROM     ...
WHERE    ...
GROUP BY ...

但是，还有比这更优雅的吗？(例如，可能有一个我不知道的聚合函数，它计算字段中位值的逻辑 and。)

57504 次浏览
小开
最佳答案

Since there are only two options for BIT, just use a case statement:

SELECT CASE WHEN EXISTS (SELECT 1 FROM ....) THEN 1 ELSE 0 END AS 'MinBit'
FROM ...
WHERE ...

This has the advantage of:

  • Not forcing a table scan (indexes on BIT fields pretty much never get used)
  • Short circuiting TWICE (once for EXISTS and again for the CASE)

It is a little more code to write but it shouldn't be terrible. If you have multiple values to check you could always encapsulate your larger result set (with all the JOIN and FILTER criteria) in a CTE at the beginning of the query, then reference that in the CASE statements.

小开

One option is MIN(SomeBitField+0). It reads well, with less noise (which I would qualify as elegance).

That said, it's more hack-ish than the CASE option. And I don't know anything about speed/efficiency.

小开

This query is the best solution:

SELECT CASE WHEN MIN(BitField+0) = 1 THEN 'True' ELSE 'False' END AS MyColumn
FROM MyTable

When you add the BitField+0 it would automatically becomes like int

小开

Try the following Note: Min represent And aggregate function , Max represent Or aggregate function 

SELECT   ..., MIN(case when SomeBitField=1 then 1 else 0 end), MIN(SomeBitField+0)...
FROM     ...
WHERE    ...
GROUP BY ...

same result

小开 
select min(convert(int, somebitfield))

or if you want to keep result as bit

select convert(bit, min(convert(int, somebitfield)))
小开

AVG(CAST(boolean_column AS FLOAT)) OVER(...) AS BOOLEAN_AGGREGATE

Give a fuzzy boolean :

  • 1 indicate that's all True;

  • 0 indicate that's all false;

  • a value between ]0..1[ indicate partial matching and can be some percentage of truth.

小开

This small piece of code has always worked with me like a charm:

CONVERT(BIT, MIN(CONVERT(INT, BitField))) as BitField

提交答案