使用分组依据时用于选择最高/最高计数的SQL

Question

谁可以帮助我的SQL，将返回最高计数的App_ID。我正在运行这个SQL，它返回以下数据集。

SELECT COMP_ID, APP_ID, count(*) as cnt 
FROM APP_ACCT_VIEW
GROUP BY COMP_ID, APP_ID

COMP_ID APP_ID                    CNT
cpo1000c    AT                    999
cpo1kact    AT                    895
cpo1kact    CPOPYMTS_Administrative    1020
cpo1000c    CPOPYMTS_HighValue           1900
cpo1kact    CPOPYMTS_HighValue           1020
cpo1000c    CPOPYMTS_Internal            1999
cpo1kact    CPOPYMTS_Internal            1020
cpo1kact    IRCDR                     1020
cpo1000c    IRCDR                     50

但我需要SQL来返回每个APP_ID的顶部/最高cnt，并需要输出如下所示。

COMP_ID APP_ID                    CNT
cpo1000c    AT                    999
cpo1kact    CPOPYMTS_Administrative    1020
cpo1000c    CPOPYMTS_HighValue           1900
cpo1000c    CPOPYMTS_Internal            1999
cpo1kact    IRCDR                     1020

谢谢迪鹏

Answer 1

在大多数数据库中，您可以使用row_number()函数来执行此操作：

select comp_id, app_id, cnt
from (select t.*, row_number() over (partition by app_id order by cnt desc) as seqnum
      from (SELECT COMP_ID, APP_ID, count(*) as cnt
            FROM APP_ACCT_VIEW
            GROUP BY COMP_ID, APP_ID
           ) t
     ) t
where seqnum = 1

如果该方法不可用，则必须以其他方式计算seqnum，例如相关子查询。

Answer 2

如果您使用的是SQL-Server2005或更高版本，则可以使用带有ROW_Number或DENSE_RANK函数的CTE：

WITH CTE AS
(
   SELECT COMP_ID, APP_ID, 
       CNT = COUNT(*) OVER (PARTITION BY APP_ID, COMP_ID) ,
       RN  = ROW_NUMBER() OVER (PARTITION BY APP_ID ORDER BY CNT DESC) 
    FROM APP_ACCT_VIEW
)
SELECT COMP_ID, APP_ID, CNT
FROM CTE
WHERE RN = 1

Answer 3

再多一个选择

 ;WITH cte AS
 (
  SELECT COMP_ID, APP_ID, COUNT(*) AS cnt,
         ROW_NUMBER() OVER(PARTITION BY APP_ID ORDER BY COUNT(*) DESC) AS rn
  FROM dbo.APP_ACCT_VIEW
  GROUP BY COMP_ID, APP_ID
  )
  SELECT COMP_ID, APP_ID, cnt
  FROM cte
  WHERE rn = 1

上的演示