如何在一个groupby返回的结果中找最大值

Question

假设一个成绩表有三列 ，学号sid 课程号cid 成绩grade要求：
求出每门课成绩出现最多的那个分数比如 数学课成绩得90分的人最多，那么就返回90分，语文85分的人最多，就返回85分；我的思路是，先执行
select cid ,grade,count(*) from table group by cid,grade;这样求出的结果，得到了每门课每一分数出现的次数，接下来就不太明白，怎么从这个结果集中获得出现次数最多的那个分数，
可以再group by一次吗？这个sql该怎么实现呢？请高手指点下！

Answer 1

;
with cte
as
(select cid ,grade,count(*) as cnt from table group by cid,grade)
,cte1
as
(  select *,rowid = row_number() over (partition by cid order by cid,cnt desc) from cte
)
select *
from cte1 
where rowid = 1

Answer 2

select cid ,grade,count(*) fc 
from table 
group by cid,grade
order by fc

Answer 3

与年级无关？
select cid ,count(*) fc 
from table 
group by cid
order by fc desc

Answer 4

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(sid int,cid int,grade int )
go
insert into tb
select 1,1,90 union all
select 2,1,90 union all
select 3,1,89 union all
select 1,2,90 union all
select 2,2,88 union all
select 3,2,88 
go
select cid, grade ,COUNT(*) as [count]
into #
from tb 
group by cid,grade order by cid select cid,grade 
from #  r
where [COUNT]=(select MAX([count]) from # where cid=r.cid) order by cid /*------------
cid         grade
----------- -----------
2           88
1           90
-------*/

Answer 5

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(sid int,cid int,grade int )
go
insert into tb
select 1,1,90 union all
select 2,1,90 union all
select 3,1,89 union all
select 1,2,90 union all
select 2,2,88 union all
select 3,2,88 union all
select 4,2,90
go
----这个方法是最多的人数一样多的时候 相同人数的分数都取出来
select cid, grade ,COUNT(*) as [count]
into # 
from tb 
group by cid,grade order by cid select cid,grade 
from #  r
where [COUNT] in (select MAX([count]) from # where cid=r.cid) order by cid /*------------(4 行受影响)
cid         grade
----------- -----------
1           90
2           88
2           90
*/
--这个方法是最多的人数一样多的时候 任取一个
select cid, grade ,COUNT(*) as [count]
into # 
from tb  
group by cid,grade order by cid select cid,max(grade) as grade  
from #  r
where [COUNT] =(select  MAX([count]) from # where cid=r.cid ) 
group by cid /*
cid         grade
----------- -----------
1           90
2           90
*/

Answer 6

select cid ,grade,count(*) as 数量 
into newTable 
from table group by cid,gradeselect cid ,grade,数量 
from newTable as A
where 数量 in 
(select max(数量) from newTable where cid=a.cid)

Answer 7

这个题目我只是个假设，不严谨，暂且考虑考虑每门课的成绩肯定有相同的。用临时表保存第一次查询的结果，是个好办法。可否直接用子查询直接取出结果了？这样使用子查询，是否可以？
select cid , grade ,max(grade_num)
from table
where cid in 
       (select cid, grade, count(*) as grade_num from table group by cid,grade order by cid )
group by cid创建一个视图也是可行吧？
create View grade_view as
select cid, grade, count(*) as grade_num from table group by cid,grade order by cidselect cid ,grade, max(grade_num)
from grade_view
group by cid

Answer 8

试试这个
select * from (select max(rs) 'rs',cid
from (select count(sid) 'rs',cid,grade
from table
group by cid,grade)ts group by cid) ta join (select count(sid) 'rs',cid,grade
from table
group by cid,grade)
tb on ta.rs=tb.rs and ta.cid=tb.cid