统计全校各班级平均分在本班人数前60%的学生名单(发过类似贴,但没说清楚,请原谅),先感谢了!

Question

上个总是没说清楚,在这重新发一下,请原谅.背景:数据格式如 成绩表(全校各班级学生成绩).
问题:想按平均成绩由高到低排序,显示平均成绩在本班前60%的学生名单(显示结果如:学号 班级 平均分),请前辈指点下!小弟先感谢了!成绩表
学号            班级    课程     成绩
20090001        一班    课程一     80
20090001        一班    课程二     50
20090001        一班    课程三     40
20090002        一班    课程一     80
20090002        一班    课程二     70
20090002        一班    课程三     80
20090003        一班    课程四     90
20090003        一班    课程五     40
20090003        一班    课程六     60
20090004        二班    课程一     50
20090004        二班    课程二     50
20090004        二班    课程三     70
20090005        二班    课程一     30
20090005        二班    课程二     70
20090005        二班    课程三     60

Answer 1

declare @成绩表 table (学号 int,班级 varchar(4),课程 varchar(6),成绩 int)
insert into @成绩表
select 20090001,'一班','课程一',80 union all
select 20090001,'一班','课程二',50 union all
select 20090001,'一班','课程三',40 union all
select 20090002,'一班','课程一',80 union all
select 20090002,'一班','课程二',70 union all
select 20090002,'一班','课程三',80 union all
select 20090003,'一班','课程四',90 union all
select 20090003,'一班','课程五',40 union all
select 20090003,'一班','课程六',60 union all
select 20090004,'二班','课程一',50 union all
select 20090004,'二班','课程二',50 union all
select 20090004,'二班','课程三',70 union all
select 20090005,'二班','课程一',30 union all
select 20090005,'二班','课程二',70 union all
select 20090005,'二班','课程三',60
select  学号,班级,avg(成绩) as 平均分 from @成绩表 group by 班级,学号 order by  avg(成绩) desc

Answer 2

select top 60 PERCENT *
from (select 学号,班级,avg(成绩) as 平均分
from 成绩表 group by 学号,班级
) as t
order by 平均分 desc

Answer 3

declare @成绩表 table (学号 int,班级 varchar(4),课程 varchar(6),成绩 int)
insert into @成绩表
select 20090001,'一班','课程一',80 union all
select 20090001,'一班','课程二',50 union all
select 20090001,'一班','课程三',40 union all
select 20090002,'一班','课程一',80 union all
select 20090002,'一班','课程二',70 union all
select 20090002,'一班','课程三',80 union all
select 20090003,'一班','课程四',90 union all
select 20090003,'一班','课程五',40 union all
select 20090003,'一班','课程六',60 union all
select 20090004,'二班','课程一',50 union all
select 20090004,'二班','课程二',50 union all
select 20090004,'二班','课程三',70 union all
select 20090005,'二班','课程一',30 union all
select 20090005,'二班','课程二',70 union all
select 20090005,'二班','课程三',60
select top 60 percent  学号,班级,avg(成绩) as 平均分 from @成绩表 group by 班级,学号 order by  avg(成绩) desc
/*
学号          班级   平均分
----------- ---- -----------
20090002    一班   76
20090003    一班   63
20090004    二班   56
*/

Answer 4

create table t ([学号] int,[班级] varchar(6),[课程] varchar(4),[成绩] int)
insert t
select 20090001,'计算机','英语',60 union all
select 20090001,'计算机','体育',70 union all
select 20090001,'计算机','语文',80 union all
select 20090002,'计算机','英语',60 union all
select 20090002,'计算机','体育',75 union all
select 20090002,'计算机','语文',80 Union all
select 20090003,'计算机','英语',69 union all
select 20090003,'计算机','体育',78 union all
select 20090003,'计算机','语文',80 union all
select 20090004,'计算机','英语',64 union all
select 20090004,'计算机','体育',75 union all
select 20090004,'计算机','语文',82 Union all
select 20090005,'计算机','英语',60 union all
select 20090005,'计算机','体育',71 union all
select 20090005,'计算机','语文',80 union all
select 20090011,'会计','英语',60 union all
select 20090011,'会计','体育',75 union all
select 20090011,'会计','语文',80 Union all
select 20090012,'会计','英语',61 union all
select 20090012,'会计','体育',75 union all
select 20090012,'会计','语文',83 Union all
select 20090013,'会计','英语',60 union all
select 20090013,'会计','体育',74 union all
select 20090013,'会计','语文',86 Union all
select 20090014,'会计','英语',61 union all
select 20090014,'会计','体育',73 union all
select 20090014,'会计','语文',84 Union all
select 20090015,'会计','英语',63 union all
select 20090015,'会计','体育',75 union all
select 20090015,'会计','语文',85 
select distinct a.学号
from t a,
     (select top 60 percent 学号,avg(成绩) as av from t group by 班级,学号 order by av desc ) b
where a.学号=b.学号
/***********
20090003
20090004
20090012
20090013
20090014
20090015***********************/

Answer 5

declare @成绩表 table (学号 int,班级 varchar(4),课程 varchar(6),成绩 int)
insert into @成绩表
select 20090001,'一班','课程一',80 union all
select 20090001,'一班','课程二',50 union all
select 20090001,'一班','课程三',40 union all
select 20090002,'一班','课程一',80 union all
select 20090002,'一班','课程二',70 union all
select 20090002,'一班','课程三',80 union all
select 20090003,'一班','课程四',90 union all
select 20090003,'一班','课程五',40 union all
select 20090003,'一班','课程六',60 union all
select 20090004,'二班','课程一',50 union all
select 20090004,'二班','课程二',50 union all
select 20090004,'二班','课程三',70 union all
select 20090005,'二班','课程一',30 union all
select 20090005,'二班','课程二',70 union all
select 20090005,'二班','课程三',60
select  *
from (select 学号,班级,avg(成绩) as 平均分
from @成绩表 group by 学号,班级
) as t
where 学号 in (
select top 60 PERCENT 学号
from (select 学号,班级,avg(成绩) as 平均分
from @成绩表 group by 学号,班级
) as t1
where 班级 = t.班级
)
order by 平均分 desc--结果
学号          班级   平均分
----------- ---- -----------
20090002    一班   76
20090001    一班   56
20090004    二班   56
20090005    二班   53(4 行受影响)

Answer 6

declare @成绩表 table (学号 int,班级 varchar(4),课程 varchar(6),成绩 int)
insert into @成绩表
select 20090001,'一班','课程一',80 union all
select 20090001,'一班','课程二',50 union all
select 20090001,'一班','课程三',40 union all
select 20090002,'一班','课程一',80 union all
select 20090002,'一班','课程二',70 union all
select 20090002,'一班','课程三',80 union all
select 20090003,'一班','课程四',90 union all
select 20090003,'一班','课程五',40 union all
select 20090003,'一班','课程六',60 union all
select 20090004,'二班','课程一',50 union all
select 20090004,'二班','课程二',50 union all
select 20090004,'二班','课程三',70 union all
select 20090005,'二班','课程一',30 union all
select 20090005,'二班','课程二',70 union all
select 20090005,'二班','课程三',60
select top 60 percent  学号,班级,avg(成绩) as 平均分  from @成绩表 group by 班级,学号 order by  avg(成绩) desc
/*
20090002 一班 76
20090003 一班 63
20090004 二班 56
*/

Answer 7

declare @t table(学号 int,班级 varchar(20),    课程 varchar(20),    成绩 int)insert into @t 
select 20090001,'一班','课程一', 80 union all
select 20090001,'一班','课程二', 50 union all
select 20090001,'一班','课程三', 40 union all
select 20090002,'一班','课程一', 80 union all
select 20090002,'一班','课程二', 70 union all
select 20090002,'一班','课程三', 80 union all
select 20090003,'一班','课程四', 90 union all
select 20090003,'一班','课程五', 40 union all
select 20090003,'一班','课程六', 60 union all
select 20090004,'二班','课程一', 50 union all
select 20090004,'二班','课程二', 50 union all
select 20090004,'二班','课程三', 70 union all
select 20090005,'二班','课程一', 30 union all
select 20090005,'二班','课程二', 70 union all
select 20090005,'二班','课程三', 60 
select top 60 percent    班级,学号, 平均分 from 
(select  distinct 班级,学号 ,平均分=(select avg(成绩) from @t where 班级=a.班级 and 学号=a.学号) from @t a) m 
order by 平均分 desc/*班级                   学号          平均分
-------------------- ----------- -----------
一班                   20090002    76
一班                   20090003    63
二班                   20090004    56(3 行受影响)*/

Answer 8

select 班级,学号 
  from (
  select 班级,学号, row_number() over(partition by 班级,学号 order by 平均分 desc) rn
    from (
       select 班级,学号,avg(成绩) 平均分
           from 成绩表
          group by 班级,学号
        ) a
       ) b,(select 班级,count(*)*0.6 rs
               from 成绩表
              group by 班级） c
where b.班级 = c.班级
  and b.rn < c.rs
order by rn

Answer 9

已经正解了，但是需要考虑在60%这个底线的人员，及分数相同的人员，所以此处应该考虑使用WITH TIES关键字。--****************************
--课程内容：关于TOP的使用方法
--讲师讲解：高建刚
--示例日期：2009-5-10
--****************************
--创建测试环境
CREATE TABLE TAB1(ID INT,RQ DATETIME)
--插入测试数据
DECLARE @int INT
SET @int=1
WHILE @int<100
BEGIN
   INSERT TAB1
   SELECT @int,GETDATE()
   SET @int=@int+1
END
--查看测试表结果
--基础的TOP使用
SELECT TOP (10) ID,RQ
FROM TAB1
ORDER BY ID
--TOP PERCENT的使用
SELECT TOP (3) PERCENT ID
FROM TAB1
--TOP中使用WITH TIES
--插入重复数据
INSERT TAB1
SELECT '9',GETDATE()
SELECT TOP 10 WITH TIES *
FROM TAB1
ORDER BY ID
--使用变量(2005新特性)
declare @int int
set @int=9
select top (@int) *
from TAB1
--与 INSERT、UPDATE 或 DELETE 一起使用的 TOP 表达式中被引用行将不按任何顺序排列。
--TOP n 随机返回 n 行。
CREATE TABLE TAB2(ID INT)
INSERT TOP (2) INTO TAB2
SELECT ID 
FROM TAB1
ORDER BY ID--ORDER BY 将失去作用
--检测结果
SELECT * FROM TAB2
--插入排序的数据时应该使用以下语句
INSERT INTO TAB2
SELECT TOP (2) ID 
FROM TAB1
ORDER BY ID
--删除测试数据
TRUNCATE TABLE TAB1
TRUNCATE TABLE TAB2
--删除测试表
DROP TABLE TAB1
DROP TABLE TAB2--注意的问题：
--1.使用WITH TIES时必须含有ORDER BY子句
--2.查询中的 TOP 表达式不影响由于触发器被激发而执行的语句

Answer 10

--生成测试数据
IF OBJECT_ID('成绩表') IS NOT NULL DROP TABLE [成绩表]
CREATE TABLE [成绩表] (学号 VARCHAR(15),班级 VARCHAR(20),课程 VARCHAR(10),成绩 float)
INSERT INTO [成绩表]
SELECT '20090001','一班','课程一',80  UNION ALL
SELECT '20090001','一班','课程二',50  UNION ALL
SELECT '20090001','一班','课程三',40  UNION ALL
SELECT '20090002','一班','课程一',80  UNION ALL
SELECT '20090002','一班','课程二',70  UNION ALL
SELECT '20090002','一班','课程三',80  UNION ALL
SELECT '20090003','一班','课程四',90  UNION ALL
SELECT '20090003','一班','课程五',40  UNION ALL
SELECT '20090003','一班','课程六',60  UNION ALL
SELECT '20090004','二班','课程一',50  UNION ALL
SELECT '20090004','二班','课程二',50  UNION ALL
SELECT '20090004','二班','课程三',70  UNION ALL
SELECT '20090005','二班','课程一',30  UNION ALL
SELECT '20090005','二班','课程二',70  UNION ALL
SELECT '20090005','二班','课程三',60 
go
--SQL语句
select top 60 percent 学号,班级,平均分 
from (select DISTINCT(a.学号),a.班级,b.平均分 from 成绩表 a,(select 学号,avg(成绩)平均分 from 成绩表  group by 学号) b
where a.学号=b.学号 ) temp order by  平均分 desc/*学号                   班级          平均分
-------------------- ----------- -----------
20090002                 一班     76.6666666666667
20090003                 一班     63.33333333333333
20090004                 二班     56.6666666666667(3 行受影响)*/

Answer 11

--測試數據
declare @成绩表 table (学号 int,班级 varchar(4),课程 varchar(6),成绩 int)
insert into @成绩表
select 20090001,'一班','课程一',80 union all
select 20090001,'一班','课程二',50 union all
select 20090001,'一班','课程三',40 union all
select 20090002,'一班','课程一',80 union all
select 20090002,'一班','课程二',70 union all
select 20090002,'一班','课程三',80 union all
select 20090003,'一班','课程四',90 union all
select 20090003,'一班','课程五',40 union all
select 20090003,'一班','课程六',60 union all
select 20090004,'二班','课程一',50 union all
select 20090004,'二班','课程二',50 union all
select 20090004,'二班','课程三',70 union all
select 20090005,'二班','课程一',30 union all
select 20090005,'二班','课程二',70 union all
select 20090005,'二班','课程三',60 
--結果
select top 60 percent *
from (select 班级,学号,AVG(成绩) as 平均成績 from @成绩表 group by 班级,学号) t1
order by 平均成績 desc

Answer 12

declare @tb table (学号 int,班级 varchar(4),课程 varchar(6),成绩 int)
insert into @tb
select 20090001,'一班','课程一',80 union all
select 20090001,'一班','课程二',50 union all
select 20090001,'一班','课程三',40 union all
select 20090002,'一班','课程一',80 union all
select 20090002,'一班','课程二',70 union all
select 20090002,'一班','课程三',80 union all
select 20090003,'一班','课程四',90 union all
select 20090003,'一班','课程五',40 union all
select 20090003,'一班','课程六',60 union all
select 20090004,'二班','课程一',50 union all
select 20090004,'二班','课程二',50 union all
select 20090004,'二班','课程三',70 union all
select 20090005,'二班','课程一',30 union all
select 20090005,'二班','课程二',70 union all
select 20090005,'二班','课程三',60select top 60 percent  学号 , 班级 ,avg(成绩)as 平均分 from @tb group by 学号,班级 order by 平均分 desc
学号          班级   平均分         
----------- ---- ----------- 
20090002    一班   76
20090003    一班   63
20090004    二班   56（所影响的行数为 3 行）

Answer 13

我靠.好快
select top 60 PERCENT * from (select 学号,班级,avg(成绩) as 平均分 from 成绩表 group by 学号,班级 ) as t order by 平均分 desc

Answer 14

select top 60 percent  学号,班级,avg(成绩) as 平均分 from @成绩表 group by 班级,学号 order by  avg(成绩) desc