带条件的 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 相关联的适当组件。

- gmt

Answer 1

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

WHERE [CompName] NOT LIKE 'BB%'

- gotqn

Answer 2

不需要删除 MAX，只需将您的条件添加到 where 子句中：

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]
  WHERE CompName NOT LIKE 'BB%'
  GROUP BY (InfoID)

  order by InfoID desc

- apomene