求一个SQL：计算每一科目出现次数最多的分数

kenshin

2013/8/15镜像同步5 回复

在网上经常看到这个题目：一个学生成绩表，其中一列是科目，一列是分数，求每一个科目中出现次数最多的分数。譬如语文有3个人考80分的，有1个人考90分的，那么语文的就是80分。怎么解呢？还是说原题不是这个样子的，而是说多少个科目多少列？

订阅后，新回复会通过你的通知中心匿名送达。

5 条回复

erickies机器人#1 · 2013/8/15

DECLARE @score TABLE( Name NVARCHAR(50), Score INT ) DECLARE @Cscore TABLE ( Name NVARCHAR(50), Score INT, [count] INT ) INSERT INTO @score VALUES(N'语文',80) INSERT INTO @score VALUES(N'语文',80) INSERT INTO @score VALUES(N'语文',80) INSERT INTO @score VALUES(N'语文',90) INSERT INTO @score VALUES(N'语文',90) INSERT INTO @score VALUES(N'数学',60) INSERT INTO @score VALUES(N'数学',70) INSERT INTO @score VALUES(N'数学',70) INSERT INTO @score VALUES(N'数学',80) INSERT INTO @score VALUES(N'英语',20) INSERT INTO @score VALUES(N'英语',30) INSERT INTO @Cscore SELECT Name,Score,Count(1)AS [count] FROM @score GROUP BY Name, Score SELECT Name,Score FROM @CScore a WHERE [Count]=(SELECT Max([Count]) FROM @CScore c WHERE c.Name= a.Name)

kenshin机器人#2 · 2013/8/15

【在 erickies 的大作中提到: 】 : DECLARE @score TABLE( : Name NVARCHAR(50), : Score INT : ................... 多谢！看来是必须临时表了。

abeibei机器人#3 · 2013/8/15

select C.course, score,C.num from (select course,max(num) as num from (select course,score,count(*) as num from #test group by course,score) B group by course) C left join (select course,score,count(*) as num from #test group by course,score) B on C.course = B.course and C.num = B.num

kenshin机器人#4 · 2013/8/16

【在 abeibei 的大作中提到: 】 : select C.course, score,C.num from : (select course,max(num) as num from (select course,score,count(*) as num from #test group by course,score) B group by course) C : left join : ................... left join 嗯。

slr123机器人#5 · 2013/9/10

SELECT MAX(DB_COUNT),SCORE FROM (SELECT COUNT(*) DB_COUNT,SCORE FROM TABLE WHERE SCORE IN (SELECT DISTINCT(SCORE) FROM TABLE)) ;没带电脑回来，没有验证，不知道对不对