如何在 SELECT 部分中包含 BIT 类型列而不将其包含在 T-SQL 中的 GROUP BY 中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6053840/
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
How to include BIT type column in SELECT part with out including it on the GROUP BY in T-SQL?
提问by Mithun Sreedharan
Here is my T-SQL query
这是我的 T-SQL 查询
SELECT
ProductID,
VendorID,
ProductName= MAX(ProductName),
VendorName = MAX(VendorName),
IsActive = MAX(IsActive) # This brings error
FROM ProductVendorAssoc
GROUP BY
ProductID,
VendorID
I want to apply GROUP BYonly for the ProductID and VendorIDfields, but need to populate the ProductID, VendorID, ProductName, VendorName, IsActivefields.
我想申请GROUP BY仅用于ProductID and VendorID字段，但需要填充ProductID, VendorID, ProductName, VendorName, IsActive字段。
Here I used the agreggate function MAX(ProductName)to avoid ProductNamein the group by list.
这里我使用了 agreggate 函数MAX(ProductName)来避免ProductName在 group by list 中。
But the same trick is not working for BITcolumns as operand data type bit is invalid for max operator.
但同样的技巧不适用于BIT列，因为操作数数据类型位对 max 运算符无效。
How can i include BITtype column in SELECT part with out including it on the GROUP BY?
如何BIT在SELECT 部分中包含类型列而不将其包含在GROUP BY?
Update.
更新。
What should I need to do if i need to include an INTcolumn like UserIDin SELECTin the same way
我应该怎么需要，如果我需要包括做INT像列UserID在SELECT以同样的方式
回答by Damien_The_Unbeliever
Put a CASE expression in there, or convert it to int:
将 CASE 表达式放入其中，或将其转换为 int：
IsActive = MAX(CASE WHEN IsActive=1 THEN 1 ELSE 0 END)
or,
或者，
IsActive = MAX(CONVERT(int,IsActive))
You should also be aware, obviously, that this means that the values in the ProductName, VendorName and IsActive columns in the result set may all come from different rows in the base table.
显然，您还应该知道，这意味着结果集中 ProductName、VendorName 和 IsActive 列中的值可能都来自基表中的不同行。
If you want those three columns to actually all be from the same row (and assuming SQL Server 2005 or later), you'd do something like:
如果您希望这三列实际上都来自同一行（并假设是 SQL Server 2005 或更高版本），您可以执行以下操作：
;With Numbered as (
SELECT *,ROW_NUMBER() OVER (
PARTITION BY ProductID,VendorID
ORDER BY /* Something appropriate, or if we just want random... */ newid()) as rn
FROM ProductVendorAssoc
)
select
ProductID,
VendorID,
ProductName,
VendorName,
IsActive
FROM Numbered where rn=1
回答by Mike Keskinov
Shorter way to do this:
更短的方法来做到这一点：
IsActive = MAX(0+IsActive)
回答by Alex Aza
In SQL2005/2008 this would be look like this:
在 SQL2005/2008 中，这看起来像这样：
select ProductId, VendorId, ProductName, VendorName, IsActive
from
(
select *, row_number() over (partition by ProductId, VendorId order by ProductId) RowNumber
from Production.Product
) tt
where RowNumber = 1
