sql-server - 限制查询中的记录

Question

这是我的查询

SELECT 
  [MO].[Id] AS [ObjectId],
  [CA].[Id] As [CategoryId],
  [GR].[Id] AS [GroupId]
FROM [MYOBJECT] AZ [MO]
INNER JOIN [Category] AS [CA] ON [CA].[Id] = [MO].[CategoryId]
Inner Join [Group] AS [GR] ON [GR].[CategoryId] = [CA].[ID]

结果是:

ObjectId    CategoryId   GroupId
-----------------------------------
1             1           1
1             2           2
2             1           1
2             2           2

但是我需要 ObjectId 上的 Distinct 我的意思是 categoryid 和 groupid 对我来说并不重要，我需要如下所示的内容:

 ObjectId    CategoryId   GroupId
-----------------------------------
1             1           1
2             1           1

或者

ObjectId    CategoryId   GroupId
-----------------------------------
1             2           2
2             1           1

以上两个结果对我来说都很好，而且正如您所看到的那样，它们都是真实记录(如您在第一个查询中看到的)

那么，我怎样才能得到这个结果呢？最快的方法是什么？有什么建议吗？

Answer 1

使用GROUP BY并在其他列上使用一些聚合函数:

SELECT 
  [MO].[Id] AS [ObjectId],
  MIN([CA].[Id]) As [CategoryId],
  MIN([GR].[Id]) AS [GroupId]
FROM [MYOBJECT] AZ [MO]
INNER JOIN [Category] AS [CA] ON [CA].[Id] = [MO].[CategoryId]
Inner Join [Group] AS [GR] ON [GR].[CategoryId] = [CA].[ID]
GROUP BY [MO].[Id]