sql-server - 带条件的 SQL Server GROUP BY

Question

在 SQL Server 中，我有这张表:

PlanID  InfoID  Comp    CompName    GuidID  Object
629293  196672  42256   AAA         7       26
629294  196672  42256   AAA         7       24
629295  196672  10000   BBZ         7       21
629296  196673  09023   CCC         7       12
629297  196673  10001   BBY         7       14
629298  196674  09027   DDS         7       16
629299  196674  10004   BBH         1       12

我想按 InfoID 分组(每个 InfoID 一行)，始终选择 CompName != BBx(注意:BBx 始终列在我感兴趣的 CompName 下，无论按字母顺序还是 Comp 值):

PlanID  InfoID  Comp    CompName    GuidID  Object
629293  196672  42256   AAA         7       26
629296  196673  09023   CCC         7       14
629298  196674  09027   DDS         7       16

我使用了这段代码:

SELECT TOP (10000) MAX (PlanID) AS [PlanID]
  ,MAX (InfoID) AS [InfoID]
  ,MAX (Comp) AS [Comp] 
  ,MAX (CompName) AS [CompName]
  ,MAX (GuidID) AS [GuidID]
  ,MAX (Object) AS [Object]

  FROM [Prod].[dbo].[Panel]
  GROUP BY (InfoID)
  order by InfoID desc

这当然会导致:

PlanID  InfoID  Comp    CompName    GuidID  Object
629293  196672  42256   BBZ         7       26
629296  196673  10001   CCC         7       14
629298  196674  10004   DDS         7       16

我应该用什么代替 MAX (CompName)？很高兴:还可以选择链接到“非 BBx”CompName 的正确 Comp。

Answer 1

只需添加一个WHERE子句来过滤您不需要包含在分组中的CompName:

WHERE [CompName] NOT LIKE 'BB%'