Sql 年级各班的前20%学生平均分

[asdf13225]

年级各班平均分语句为：
Select AVG(数学) from table Group by 班级 Order by 班级 ASC
但现在我要以班排为依据(升序) ，算出每个班前 20% 的学生各班的平均分，怎么写？

Select top 20 percent AVG(数学) from table Group by 班级 Order by 班级 ASC
得到的还是班级平均分(显示的是上面结果的 20%)，注意是’所有班‘，不是某一个班。

[asdf13225]

回复：首先，谢谢大家这么热心，快速的解答。我验算了一下，发现有以下问题： roy_88 1楼：（执行的返回结果是): 消息 8120，级别 16，状态 1，第 1 行 选择列表中的列 'Table.数学' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。 roy_88 2楼： 没有报错，但得到的结果，所有班数据都是一样的。并且也不是班排前20%的平均值。 KanzakiOrange：完全看不懂，估计也不行。（有tb,tb1,tb2)?

[asdf13225]

注：班排不是指 数学按从高分到低分的排名，而是总分（各科之和）按从高分到低分的排名，班排已经有数据（为1，2，3...）。

[中国风]

SELECT AVG(数学) FROM (Select top 20 percent 数学 from [table] Group by 班级 Order by 班级 ASC) AS T

[中国风]

SELECT a.班级,AVG(数学) AS 数学 FROM (SELECT DISTINCT 班级 FROM [table] )AS a CROSS APPLY(SELECT top 20 PERCENT 数学 FROM [table] ORDER BY 数学 DESC) AS b GROUP BY a.班级 ORDER BY 数学 DESC

[Ginnnnnnnn]

取20%，我这里投了一个机。但是版主那个比较合理

;WITH tb(班级,学生,数学) AS
(
	SELECT 1,'A',80
	UNION ALL
	SELECT 1,'B',90
	UNION ALL
	SELECT 1,'C',60
	UNION ALL
	SELECT 1,'D',70
	UNION ALL
	SELECT 1,'E',85
	UNION ALL
	SELECT 2,'F',80
	UNION ALL
	SELECT 2,'G',80
	UNION ALL
	SELECT 2,'H',80
	UNION ALL
	SELECT 1,'I',90
	UNION ALL
	SELECT 1,'J',60
	UNION ALL
	SELECT 1,'K',70
	UNION ALL
	SELECT 1,'L',85
	UNION ALL
	SELECT 1,'M',80
),tb1 AS(
SELECT *,
		NTILE(5) OVER (PARTITION BY 班级 ORDER BY 数学) AS RNr
	FROM tb)
SELECT 班级,AVG(数学)
	FROM tb2
		WHERE tb1.RNr = 1
	GROUP BY 班级
    

[道玄希言]

引用 4 楼 asdf13225 的回复:

KanzakiOrange：完全看不懂，估计也不行。（有tb,tb1,tb2)?

KanzakiOrange 采用的是先将数据分片， 然后再统计， 不过他的SQL语句中，有个地方， 应该是笔误了， tb1 写成了 tb2 估计楼主你还是在 SQL 2000 吧， 所以看不懂4楼的代码了。 2005 以及以后的版本， 都能这么写的。 第一个 tb ， 是造了个模拟的数据； 第二个 tb1， 是将 tb 的数据分片； 不明白请查 NTILE 函数 SELECT 班级,AVG(数学) FROM tb1 WHERE tb1.RNr = 1 GROUP BY 班级 这句就是查询分片中，第一片数据的平均值。

[asdf13225]

对不起，是我没有说的明白，现在我列出 Table 的字段名和数据说明。 班级, 学号, 姓名, 语文, 数学, 英语,物理,化学,生物,理综, 总分,班排,三月考 班级有1，2，3...学号为1，2，3...语文等各科，班排，三月考都有数据，(表按班级,学号升序排列) 现在求每班前20%的数学平均分（即每班排名为前20%的数学平均分） 例如1班数学平均分为 108.629629（这是该班所有学生的平均分），那么1班前20% （假设该班有60人，那么20%就是班排前12名即班排=1-12名的数学平均分） 不知道说明清楚了没（啰嗦了一大堆） 我从网上看到类似的Sql语句，修改后经过验证可行。如果大家有其它的语句，也可以贴出来。

select AVG(数学) from Table t where 学号 in(select top 20 percent 学号 from Table where 班级=t.班级 order by 班排 ASC) Group by 班级

[spiritofdragon]

引用 9 楼 asdf13225 的回复:

对不起，是我没有说的明白，现在我列出 Table 的字段名和数据说明。 班级, 学号, 姓名, 语文, 数学, 英语,物理,化学,生物,理综, 总分,班排,三月考 班级有1，2，3...学号为1，2，3...语文等各科，班排，三月考都有数据，(表按班级,学号升序排列) 现在求每班前20%的数学平均分（即每班排名为前20%的数学平均分） 例如1班数学平均分为 108.629629（这是该班所有学生的平均分），那么1班前20% （假设该班有60人，那么20%就是班排前12名即班排=1-12名的数学平均分） 不知道说明清楚了没（啰嗦了一大堆） 我从网上看到类似的Sql语句，修改后经过验证可行。如果大家有其它的语句，也可以贴出来。

select AVG(数学) from Table t where 学号 in(select top 20 percent 学号 from Table where 班级=t.班级 order by 班排 ASC) Group by 班级

我理解不对，这是全年级所有人放一起，然后班排，取全年级前20%，然后，再各个班自己内部取平均值

[spiritofdragon]

引用 10 楼 spiritofdragon 的回复:

[quote=引用 9 楼 asdf13225 的回复:] 对不起，是我没有说的明白，现在我列出 Table 的字段名和数据说明。 班级, 学号, 姓名, 语文, 数学, 英语,物理,化学,生物,理综, 总分,班排,三月考 班级有1，2，3...学号为1，2，3...语文等各科，班排，三月考都有数据，(表按班级,学号升序排列) 现在求每班前20%的数学平均分（即每班排名为前20%的数学平均分） 例如1班数学平均分为 108.629629（这是该班所有学生的平均分），那么1班前20% （假设该班有60人，那么20%就是班排前12名即班排=1-12名的数学平均分） 不知道说明清楚了没（啰嗦了一大堆） 我从网上看到类似的Sql语句，修改后经过验证可行。如果大家有其它的语句，也可以贴出来。

select AVG(数学) from Table t where 学号 in(select top 20 percent 学号 from Table where 班级=t.班级 order by 班排 ASC) Group by 班级

我理解不对，这是全年级所有人放一起，然后班排，取全年级前20%，然后，再各个班自己内部取平均值[/quote] 赶紧自打脸，省得别人动手。这句是对了。可以用。是每班内排20%。然后，再每班内部取数学平均值。

[asdf13225]

没有想到各位这么热心，我深受感动。谢谢大家！

[spiritofdragon]

引用 5 楼 asdf13225 的回复:

注：班排不是指 数学按从高分到低分的排名，而是总分（各科之和）按从高分到低分的排名，班排已经有数据（为1，2，3...）。

。。。各科之和。。。，在1楼你的sql中哪体现了还有别的科，谁都以为只用算数学，现在说“班排”的含义，不是扯乎么。。。先给你的数据结构和测试数据和想要的结果。。。不然谁知道你的问题描述全了没！！

[中国风]

引用 5 楼 asdf13225 的回复:

注：班排不是指 数学按从高分到低分的排名，而是总分（各科之和）按从高分到低分的排名，班排已经有数据（为1，2，3...）。

--少了WHERE 条件加上

SELECT  a.班级
       ,AVG(数学) AS 数学
FROM    ( SELECT DISTINCT
                    班级
          FROM      [table]
        ) AS a
        CROSS APPLY ( SELECT TOP 20 PERCENT
                                数学
                      FROM      [table]
					  WHERE 班级=a.班级
                      ORDER BY  数学 DESC
                    ) AS b
GROUP BY a.班级
ORDER BY 数学 DESC;

[大壮vip]

可以的，长见识了