问题描述
我想编写以下查询:
SELECT ..., MIN(SomeBitField), ...
FROM ...
WHERE ...
GROUP BY ...
问题是, 返回错误 Operand data type bit is invalid for min operator.
The problem is, 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 theCASE
)
要编写更多的代码,但应该不会太糟糕.如果您有多个要检查的值,您可以始终将较大的结果集(带有所有
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 字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!