sql - 从Group by获取最大数量

我在sql中无法获取组函数的输出。以下是表的详细信息

我有1个表，表名“checks”有2列pid，cid

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PID                                                VARCHAR2(20)
 CID                                                VARCHAR2(20)

以下是可用的行

select * from checks;

PID                  CID
-------------------- --------------------
p1                   c1
p1                   c1
p1                   c2
p1                   c2
p1                   c2
p2                   c1
p2                   c1
p2                   c1
p2                   c1
p2                   c1
p2                   c1
p2                   c2
p2                   c2
p2                   c2
p2                   c2
p2                   c2

P代表参与者，c代表类别

问题

我需要知道哪个参与者最多参与那个类别中的哪个类别。(对于每个参与者)

预期结果:

pid   cid    count(cid)
---   ---    -----------
p1    c2         3
p2    c1         6

最佳答案

假设一个支持窗口功能和CTE的数据库系统(您尚未指定一个，但我怀疑是Oracle？)，我将这样写:

;With Groups as (
    select pid,cid,COUNT(*) as cnt from checks group by pid,cid
), Ordered as (
    select pid,cid,cnt,
       ROW_NUMBER() OVER (PARTITION BY pid ORDER BY cnt desc) as rn,
       COUNT(*) OVER (PARTITION BY pid) as multi
    from Groups
)
select pid,cid,cnt
from Ordered
where rn = 1 and multi > 1

第一个CTE(Groups)仅查找每个唯一cid,pid组合的计数。第二个CTE(Ordered)根据计数为这些结果分配行号-最高计数为行号1。我们还计算每个pid产生了多少行。

最后，我们选择分配了行号1(最高计数)的行，并针对相同的pid获得了多个结果。

这是一个Oracle fiddle可以玩。这是一个SQL Server version(感谢Andriy M产生了Oracle)

关于sql - 从Group by获取最大数量，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17715825/