SQL 将 MIN 聚合函数应用于 BIT 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6997721/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Applying the MIN aggregate function to a BIT field
提问by pyon
I want to write the following query:
我想编写以下查询：
SELECT ..., MIN(SomeBitField), ...
FROM ...
WHERE ...
GROUP BY ...
The problem is, SQL Server does not like it, when I want to calculate the minimum value of a bit fieldit returns the error Operand data type bit is invalid for min operator.
问题是，SQL Server 不喜欢它，当我想计算位字段的最小值时，它返回错误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 andof the bit values in a field.)
但是，还有更优雅的东西吗？（例如，可能有一个我不知道的聚合函数，它评估and字段中位值的逻辑。）
采纳答案by JNK
Since there are only two options for BIT, just use a case statement:
由于只有两个选项BIT，只需使用 case 语句：
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
BITfields pretty much never get used) - Short circuiting TWICE (once for
EXISTSand again for theCASE)
- 不强制进行表扫描（
BIT几乎从未使用过字段索引） - 短路两次（一次
EXISTS又一次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 JOINand FILTERcriteria) in a CTEat the beginning of the query, then reference that in the CASEstatements.
需要编写更多的代码，但它不应该很糟糕。如果您有多个要检查的值，您总是可以在查询开始时将较大的结果集（包括所有JOIN和FILTER条件）封装在 aCTE中，然后在CASE语句中引用它。
回答by Ben Mosher
One option is MIN(SomeBitField+0). It reads well, with less noise (which I would qualify as elegance).
一种选择是MIN(SomeBitField+0)。它读起来很好，噪音更小（我认为这是优雅的）。
That said, it's more hack-ish than the CASEoption. And I don't know anything about speed/efficiency.
也就是说，它比CASE选项更黑客。我对速度/效率一无所知。
回答by Israel Margulies
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
当您添加 BitField+0 时，它会自动变得像 int
回答by Waleed A.K.
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
相同的结果
回答by Verard Sloggett
select min(convert(int, somebitfield))
or if you want to keep result as bit
或者如果你想保持结果
select convert(bit, min(convert(int, somebitfield)))
回答by Chaos Legion
This small piece of code has always worked with me like a charm:
这一小段代码一直对我有用：
CONVERT(BIT, MIN(CONVERT(INT, BitField))) as BitField
回答by user7370003
AVG(CAST(boolean_column AS FLOAT)) OVER(...) AS BOOLEAN_AGGREGATE
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.
1 表示全部为真；
0 表示这都是假的；
]0..1[ 之间的值表示部分匹配，并且可以是真实的某个百分比。
