SQL 唯一标识符 (GUID) 上的聚合函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6069368/
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
Aggregate Function on Uniqueidentifier (GUID)
提问by Heinzi
Let's say I have the following table:
假设我有下表：
category | guid
---------+-----------------------
A | 5BC2...
A | 6A1C...
B | 92A2...
Basically, I want to do the following SQL:
基本上，我想做以下 SQL：
SELECT category, MIN(guid)
FROM myTable
GROUP BY category
It doesn't necessarily have to be MIN. I just want to return oneGUID of each category. I don't care which one. Unfortunately, SQL Server does not allow MIN or MAX on GUIDs.
它不一定必须是 MIN。我只想返回每个类别的一个GUID。我不在乎是哪一个。不幸的是，SQL Server 不允许在 GUID 上使用 MIN 或 MAX。
Of course, I could convert the guid into a varchar, or create some nested TOP 1 SQL, but that seems like an ugly workaround. Is there some elegant solution that I've missed?
当然，我可以将 guid 转换为 varchar，或者创建一些嵌套的 TOP 1 SQL，但这似乎是一个丑陋的解决方法。有没有我错过的优雅解决方案？
采纳答案by Damien_The_Unbeliever
Assuming you're using SQL Server 2005 or later:
假设您使用的是 SQL Server 2005 或更高版本：
;with Numbered as (
select category,guid,ROW_NUMBER() OVER (PARTITION BY category ORDER BY guid) rn
from myTable
)
select * from Numbered where rn=1
回答by K Biel
Just cast it as a BINARY(16).
只需将其转换为BINARY(16).
SELECT category, MIN(CAST(guid AS BINARY(16)))
FROM myTable
GROUP BY category
You can cast it back later if necessary.
如有必要，您可以稍后将其退回。
WITH CategoryValue
AS
(
SELECT category, MIN(CAST(guid AS BINARY(16)))
FROM myTable
GROUP BY category
)
SELECT category, CAST(guid AS UNIQUEIDENTIFIER)
FROM CategoryValue
回答by doerig
Aggregate functions can be used on Uniqueidentifier columns if SQL Server Version >= 2012
如果 SQL Server 版本 >= 2012，则可以在 Uniqueidentifier 列上使用聚合函数
expression
Is a constant, column name, or function, and any combination of arithmetic, bitwise, and string operators. MIN can be used with numeric, char, varchar, uniqueidentifier, or datetime columns, but not with bit columns. Aggregate functions and subqueries are not permitted.
表达
是常量、列名或函数，以及算术、按位和字符串运算符的任意组合。MIN 可用于数字、字符、varchar、uniqueidentifier 或日期时间列，但不能用于位列。不允许使用聚合函数和子查询。
回答by Mikael Eriksson
declare @T table(category char(1), guid uniqueidentifier)
insert into @T
select 'a', newid() union all
select 'a', newid() union all
select 'b', newid()
select
S.category,
S.guid
from
(
select
T.category,
T.guid,
row_number() over(partition by T.category order by (select 1)) as rn
from @T as T
) as S
where S.rn = 1
If you are on SQL Server 2000 you could to this
如果您使用的是 SQL Server 2000，则可以这样做
select
T1.category,
(select top 1 T2.guid
from @T as T2
where T1.category = T2.category) as guid
from @T as T1
group by T1.category
回答by Abhi
SELECT top 1 category, guid FROM myTable GROUP BY category,guid
SELECT top 1 category, guid FROM myTable GROUP BY category,guid
