将 MIN 聚合函数应用于 BIT 字段 [英] Applying the MIN aggregate function to a BIT field

问题描述

我想编写以下查询:

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

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

The problem is, SQL Server does not like it, when I want to calculate the minimum value of a bit field it returns the error Operand data type bit is invalid for min operator.

我可以使用以下解决方法:

I could use the following workaround:

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

但是，还有更优雅的东西吗?(例如，可能有一个我不知道的聚合函数，它计算字段中位值的逻辑 和.)

But, is there something more elegant? (For example, there might be an aggregate function, that I don't know, and that evaluates the logical and of the bit values in a field.)

推荐答案

由于BIT只有两个选项，就用case语句:

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 ...

这样做的好处是:

• 不强制进行表扫描(BIT 字段上的索引几乎从未被使用过)
• 短路两次(EXISTS 一次，CASE 一次)

• 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)

要编写更多的代码，但应该不会太糟糕.如果您有多个要检查的值，您可以始终将较大的结果集(带有所有 JOIN 和 FILTER 条件)封装在 CTE 中查询的开头，然后在 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.

这篇关于将 MIN 聚合函数应用于 BIT 字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！