100分，求一个sql语句，各位高手帮帮忙！(还是以前的问题，寻求最佳答案)

tbterm 年级表
id,termname 年级名字

tbclass 班级表
id,classname 班级名字，termid 年级id

tbtest 考试表
id,testname 考试名字，termid

tbstudent 学生表
id,stuname 学生名字，classid 班级id

tbcourse 课程表
id，coursename 课程名字，termid 年级id

tbscore 分数表
id，stuid 学生id，courseid 课程id，testid 考试id， score 分数
--------------------------------------------
测试数据及表结构。
--------创建数据库
create database sm
go
--------使用数据库
use sm

----------创建年级表
create table tbterm
(
  id int primary key identity(1,1), ------主键
  termname varchar(20)
)

----------创建班级表
create table tbclass
(
  id int primary key identity(1,1), ------主键
  classname varchar(20),
  termid int
)

----------创建考试表
create table tbtest
(
  id int primary key identity(1,1), ------主键
  testname varchar(20),
  termid int
)

----------创建学生表
create table tbstudent
(
  id int primary key identity(1,1), ------主键
  stuname varchar(20),
  classid int
)

----------创建课程表
create table tbcourse
(
  id int primary key identity(1,1), ------主键
  coursename varchar(20),
  termid int
)

----------创建分数表
create table tbscore
(
  id int primary key identity(1,1), ------主键
  score varchar(20),
  stuid int,
  courseid int,
  testid int 
)


------------------------------插入年级
insert into tbterm(termname) values('一年级')

------------------------------插入班级
insert into tbclass(classname,termid) values('一班','1') 
insert into tbclass(classname,termid) values('二班','1')
insert into tbclass(classname,termid) values('三班','1')
------------------------------插入考试
insert into tbtest(testname,termid) values('期中考试','1')
------------------------------插入科目
insert into tbcourse(coursename,termid) values('数学','1')
------------------------------插入学生
insert into tbstudent(stuname,classid) values('stu1','1')
insert into tbstudent(stuname,classid) values('stu2','1')
insert into tbstudent(stuname,classid) values('stu3','1')
insert into tbstudent(stuname,classid) values('stu4','1')
insert into tbstudent(stuname,classid) values('stu5','1')
insert into tbstudent(stuname,classid) values('stu6','1')
insert into tbstudent(stuname,classid) values('stu7','1')
insert into tbstudent(stuname,classid) values('stu8','1')
insert into tbstudent(stuname,classid) values('stu9','1')
insert into tbstudent(stuname,classid) values('stu10','1')
insert into tbstudent(stuname,classid) values('stu11','2')
insert into tbstudent(stuname,classid) values('stu12','2')
insert into tbstudent(stuname,classid) values('stu13','2')
insert into tbstudent(stuname,classid) values('stu14','2')
insert into tbstudent(stuname,classid) values('stu15','2')
insert into tbstudent(stuname,classid) values('stu16','2')
insert into tbstudent(stuname,classid) values('stu17','2')
insert into tbstudent(stuname,classid) values('stu18','2')
insert into tbstudent(stuname,classid) values('stu19','2')
insert into tbstudent(stuname,classid) values('stu20','2')
insert into tbstudent(stuname,classid) values('stu21','3')
insert into tbstudent(stuname,classid) values('stu22','3')
insert into tbstudent(stuname,classid) values('stu23','3')
insert into tbstudent(stuname,classid) values('stu24','3')
insert into tbstudent(stuname,classid) values('stu25','3')
insert into tbstudent(stuname,classid) values('stu26','3')
insert into tbstudent(stuname,classid) values('stu27','3')
insert into tbstudent(stuname,classid) values('stu28','3')
insert into tbstudent(stuname,classid) values('stu29','3')
insert into tbstudent(stuname,classid) values('stu30','3')
------------------------------插入分数
insert into tbscore(score,stuid,courseid,testid) values(90,1,1,1)
insert into tbscore(score,stuid,courseid,testid) values(99,2,1,1)
insert into tbscore(score,stuid,courseid,testid) values(12,3,1,1)
insert into tbscore(score,stuid,courseid,testid) values(88,4,1,1)
insert into tbscore(score,stuid,courseid,testid) values(70,5,1,1)
insert into tbscore(score,stuid,courseid,testid) values(95,6,1,1)
insert into tbscore(score,stuid,courseid,testid) values(88,7,1,1)
insert into tbscore(score,stuid,courseid,testid) values(87,8,1,1)
insert into tbscore(score,stuid,courseid,testid) values(78,9,1,1)
insert into tbscore(score,stuid,courseid,testid) values(90,10,1,1)
insert into tbscore(score,stuid,courseid,testid) values(68,11,1,1)
insert into tbscore(score,stuid,courseid,testid) values(87,12,1,1)
insert into tbscore(score,stuid,courseid,testid) values(89,13,1,1)
insert into tbscore(score,stuid,courseid,testid) values(78,14,1,1)
insert into tbscore(score,stuid,courseid,testid) values(33,15,1,1)
insert into tbscore(score,stuid,courseid,testid) values(44,16,1,1)
insert into tbscore(score,stuid,courseid,testid) values(77,17,1,1)
insert into tbscore(score,stuid,courseid,testid) values(88,18,1,1)
insert into tbscore(score,stuid,courseid,testid) values(99,19,1,1)
insert into tbscore(score,stuid,courseid,testid) values(44,20,1,1)
insert into tbscore(score,stuid,courseid,testid) values(23,21,1,1)
insert into tbscore(score,stuid,courseid,testid) values(67,22,1,1)
insert into tbscore(score,stuid,courseid,testid) values(54,23,1,1)
insert into tbscore(score,stuid,courseid,testid) values(43,24,1,1)
insert into tbscore(score,stuid,courseid,testid) values(65,25,1,1)
insert into tbscore(score,stuid,courseid,testid) values(90,26,1,1)
insert into tbscore(score,stuid,courseid,testid) values(76,27,1,1)
insert into tbscore(score,stuid,courseid,testid) values(85,28,1,1)
insert into tbscore(score,stuid,courseid,testid) values(10,29,1,1)
insert into tbscore(score,stuid,courseid,testid) values(54,30,1,1)

----------我想得到的结果样子------------------
 年级   班级   考试     科目 10% 10%-25% 25%-60% 60%-90% 90%-100% 最高分 最低分 平均分
一年级  一班 开学考试   数学  1     2       4       3       0       150    21     88
一年级  二班 开学考试   数学  1     1       4       3       1       148    42     75
一年级  三班 开学考试   数学  1     1       3       3       2       150    32     78
------------
详细说明：
写语句的时候可以传三个参数，年级ID、考试ID和科目ID
我想查询出全年级所有人单科成绩前10%的人数,每个班占多少人?
去掉前10%的人,中间的10%-25%每个班又占多少人，去掉前25%，中间25%-60%每个班又占多少人等...
就是把100%拆分开，中间的10%-25%这一段的人数每个班占多少人！
注：并不是分数10-25分每个班占多少人，25-60每个班又占多少人！
最好把以下的这些条件都写好：
tbterm.id=tbclass.termid and tbterm.id=tbcourse.termid and tbterm.id=tbtest.termid 
and tbclass.id=tbstudent.classid and tbtest.id=tbscore.testid 
and tbstudent.id=tbscore.stuid and tbcourse.id=tbscore.courseid
and tbterm.id='' and tbtest.id='' and tbcourse.id=''

不理解的回复，本人在线等。

18 个解决方案

#1

关注...

#2

不是写给了的马？？

#3

不对，不是多少分到多少分。

#4

去掉前10%的人,中间的10%-25%每个班又占多少人，去掉前25%，中间25%-60%每个班又占多少人等...
就是把100%拆分开，中间的10%-25%这一段的人数每个班占多少人！

不懂

#5

还有百分比的算法。举个例子

#6

把100分成5份，0-10，10-25，25-60，60-90，90-10.
上边就相当于百分比。
在每个百分比当中，每个班占多少人？
比如说10-25这个数值之间每个班占了几个人。
例：一个年级有100个人，按单科分数排名，前10名中每个班占几个人，第10到25名中，每个班占多少人，
第25-60名中，每个班占多少人？

#7

select t1.termname, t2.classname , t3.testname,t4.coursename ,
(
select count(*) from tbscore n1,tbstudent n2
where stuid in (select top 10 percent p.stuid from tbscore p where p.courseid =n1.courseid and p.testid =n1.testid order by p.score desc)
and n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id
) as [10%],
(
select count(*) from tbscore n1,tbstudent n2
where stuid in (select top 15 stuid from (select top 25 percent p.stuid from tbscore p where p.courseid =n1.courseid and p.testid =n1.testid order by p.score desc) t order by score)
and n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id
) as [10%-25%],
(
select count(*) from tbscore n1,tbstudent n2
where stuid in (select top 35 stuid from (select top 60 percent p.stuid from tbscore p where p.courseid =n1.courseid and p.testid =n1.testid order by p.score desc) t order by score)
and n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id
) as [25%-60%],
(
select count(*) from tbscore n1,tbstudent n2
where stuid in (select top 30 stuid from (select top 90 percent p.stuid from tbscore p where p.courseid =n1.courseid and p.testid =n1.testid order by p.score desc) t order by score)
and n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id
) as [60%-90%],
(
select count(*) from tbscore n1,tbstudent n2
where stuid in (select top 10 percent p.stuid from tbscore p where p.courseid =n1.courseid and p.testid =n1.testid order by p.score )
and n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id
) as [100%],
(select max(score) from tbscore n1,tbstudent n2 where  n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id) as 最高分,
(select min(score) from tbscore n1,tbstudent n2 where  n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id) as 最低分,
(select avg(score*1) from tbscore n1,tbstudent n2 where  n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id) as 平均分
from tbterm t1, tbclass t2 , tbtest t3,tbcourse t4 
where t1.id=t2.termid
and  t1.id=t3.termid
and  t1.id=t4.termid


termname             classname            testname             coursename           10%         10%-25%     25%-60%     60%-90%     100%        最高分                  最低分                  平均分         
-------------------- -------------------- -------------------- -------------------- ----------- ----------- ----------- ----------- ----------- -------------------- -------------------- ----------- 
一年级                  一班                   期中考试                 数学                   2           5           9           9           1           99                   12                   79
一年级                  二班                   期中考试                 数学                   1           2           6           10          0           99                   33                   70
一年级                  三班                   期中考试                 数学                   0           1           3           8           2           90                   10                   56

（所影响的行数为 3 行）

#8

那天的有问题??

#9

嗯，呵呵。

#10

可惜，那天做上午做一半半，是照TOP n PENCENT来做的，下午回来后发现结贴了，以为我的想法错了，代码全关掉了。

#11

declare @termname varchar(10),@testname  varchar(10),@coursename varchar(10)
set @termname='一年级'
set @testname='期中考试'
set @coursename='数学'

;
with cte as(
SELECT @termname as termname,@testname as testname,@coursename as coursename,
a.*,c.classname,classid,rn=dense_rank() over(order by score desc)  FROM tbscore A,tbstudent B ,tbclass c 
WHERE A.stuid=B.id AND
 courseid=(SELECT ID FROM tbcourse WHERE coursename=@coursename)
and b.classid=c.id
AND a.testid=(select id from tbtest where testname=@testname)
and c.termid=(SELECT ID FROM tbterm WHERE termname=@termname)

),
cte1 as(select termname,classid,classname,testname,coursename,stuid
,max(cast(score as int)) over(partition by classname) as max_score
,min(cast(score as int)) over(partition by classname) as min_score
,avg(cast(score as int)) over(partition by classname) as avg_score
,case when rn<=max(rn) over()*0.1 then '10%'
 when rn>max(rn) over()*0.1 and  rn<=max(rn) over()*0.25 then '10%-25%'
 when rn>max(rn) over()*0.25 and rn<=max(rn) over()*0.60 then '25%-60%'
 when rn>max(rn) over()*0.60 and rn<=max(rn) over()*0.90 then '60%-90%'
 when rn>max(rn) over()*0.90 then '90%-100%'
end stat
 from cte )



select termname as 年级,classname as  班级,testname as 考试,
coursename as 科目,[10%],[10%-25%],[25%-60%],[60%-90%],[90%-100%],
max_score as 最高分,min_score as 最低分,avg_score as 平均分
from cte1
pivot (count(stuid)  for stat in([10%],[10%-25%],[25%-60%],[60%-90%],[90%-100%]))pvt
order by classid

年级         班级                   考试         科目                 10%     10%-25%     25%-60%     60%-90%    90%-100%         最高分         最低分         平均分
---------- -------------------- ---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
一年级        一班                   期中考试       数学                   2           4           3           0           1          99          12          79
一年级        二班                   期中考试       数学                   1           2           4           3           0          99          33          70
一年级        三班                   期中考试       数学                   0           1           2           5           2          90          10          56

(3 行受影响)

#12

东升哥，你这个报
消息 512，级别 16，状态 1，第 7 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
这样子的错误，小弟实在笨的很，所以。。。

#13

哦，好了，我看下结果。

#14

--准备工作
declare @total int declare @tempscore table(id int identity(1,1),score int,classname varchar(20))
insert into @tempscore 
	select score,classname 
	from tbscore,tbstudent,tbclass 
	where tbscore.stuid=tbstudent.id and tbclass.id=tbstudent.classid
	order by score desc
select @total=count(*) from tbscore group by testid,courseid
--查询语句
select 年级,t1.班级,考试,科目,col1 '10%',col2 '10%-25%',col3 '25%-60%',col4 '60%-90%',col5 '90%-100%',最高分,最低分,平均分
from(
select distinct termname 年级,classname 班级,testname 考试,coursename 科目
from tbterm,tbclass,tbtest,tbcourse
where tbterm.id=tbclass.termid and tbterm.id=tbcourse.termid and tbterm.id=tbtest.termid and tbterm.id=1  and tbtest.id=1 and tbcourse.id=1
)t1,(
select classname 班级,sum(case when id*1.0/@total<=0.1 then 1 else 0 end)col1,
sum(case when id*1.0/@total>0.1 and id*1.0/@total<=0.25 then 1 else 0 end)col2,
sum(case when id*1.0/@total>0.25 and id*1.0/@total<=0.6 then 1 else 0 end)col3,
sum(case when id*1.0/@total>0.6 and id*1.0/@total<=0.9 then 1 else 0 end)col4,
sum(case when id*1.0/@total>0.9 and id*1.0/@total<=1.0 then 1 else 0 end)col5,
max(score)最高分,
min(score)最低分,
avg(score)平均分
from @tempscore
group by classname )t2
where t1.班级=t2.班级


/*结果

 年级   班级   考试     科目	10%	10%-25% 25%-60% 60%-90% 90%-100% 最高分 最低分 平均分
-------------------------------------------------------------------------------------------------------
一年级	二班	期中考试	数学	1	1	4	4	0	99	33	70
一年级	三班	期中考试	数学	0	1	2	5	2	90	10	56
一年级	一班	期中考试	数学	2	2	5	0	1	99	12	79

*/

#15

--准备工作
declare @total int declare @tempscore table(id int identity(1,1),score int,classid int,classname varchar(20))
insert into @tempscore 
	select score,tbclass.id,classname
	from tbscore,tbstudent,tbclass 
	where tbscore.stuid=tbstudent.id and tbclass.id=tbstudent.classid
	order by score desc
select @total=count(*) from tbscore group by testid,courseid
--查询语句
select 年级,t2.班级,考试,科目,col1 '10%',col2 '10%-25%',col3 '25%-60%',col4 '60%-90%',col5 '90%-100%',最高分,最低分,平均分
from(
select distinct termname 年级,classname 班级,testname 考试,coursename 科目
from tbterm,tbclass,tbtest,tbcourse
where tbterm.id=tbclass.termid and tbterm.id=tbcourse.termid and tbterm.id=tbtest.termid and tbterm.id=1  and tbtest.id=1 and tbcourse.id=1
)t1,(
select classid,classname 班级,sum(case when id*1.0/@total<=0.1 then 1 else 0 end)col1,
sum(case when id*1.0/@total>0.1 and id*1.0/@total<=0.25 then 1 else 0 end)col2,
sum(case when id*1.0/@total>0.25 and id*1.0/@total<=0.6 then 1 else 0 end)col3,
sum(case when id*1.0/@total>0.6 and id*1.0/@total<=0.9 then 1 else 0 end)col4,
sum(case when id*1.0/@total>0.9 and id*1.0/@total<=1.0 then 1 else 0 end)col5,
max(score)最高分,
min(score)最低分,
avg(score)平均分
from @tempscore
group by classid,classname )t2
where t1.班级=t2.班级
order by t2.classid

/*结果

 年级   班级   考试     科目	10%	10%-25% 25%-60% 60%-90% 90%-100% 最高分 最低分 平均分
-------------------------------------------------------------------------------------------------------
一年级	一班	期中考试	数学	2	2	5	0	1	99	12	79
一年级	二班	期中考试	数学	1	1	4	4	0	99	33	70
一年级	三班	期中考试	数学	0	1	2	5	2	90	10	56

*/

对结果排序，看起来更舒服些....

#16

引用 15 楼 happycell188 的回复:

SQL code
--准备工作
declare @total int declare @tempscore table(id int identity(1,1),score int,classid int,classname varchar(20))
insert into @tempscore 
    select score,tbclass.id,classname
    fro……

强人啊

#17

SQL code
select t1.termname, t2.classname , t3.testname,t4.coursename ,
(
select count(*) from tbscore n1,tbstudent n2
where stuid in (select top 10 percent p.stuid from tbscore p where p.courseid =n1.courseid and p.testid =n1.testid order by p.score desc)
and n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id
) as [10%],
(
select count(*) from tbscore n1,tbstudent n2
where stuid in (select top 15 stuid from (select top 25 percent p.stuid from tbscore p where p.courseid =n1.courseid and p.testid =n1.testid order by p.score desc) t order by score)
and n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id
) as [10%-25%],
(
select count(*) from tbscore n1,tbstudent n2
where stuid in (select top 35 stuid from (select top 60 percent p.stuid from tbscore p where p.courseid =n1.courseid and p.testid =n1.testid order by p.score desc) t order by score)
and n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id
) as [25%-60%],
(
select count(*) from tbscore n1,tbstudent n2
where stuid in (select top 30 stuid from (select top 90 percent p.stuid from tbscore p where p.courseid =n1.courseid and p.testid =n1.testid order by p.score desc) t order by score)
and n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id
) as [60%-90%],
(
select count(*) from tbscore n1,tbstudent n2
where stuid in (select top 10 percent p.stuid from tbscore p where p.courseid =n1.courseid and p.testid =n1.testid order by p.score )
and n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id
) as [100%],
(select max(score) from tbscore n1,tbstudent n2 where  n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id) as 最高分,
(select min(score) from tbscore n1,tbstudent n2 where  n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id) as 最低分,
(select avg(score*1) from tbscore n1,tbstudent n2 where  n1.stuid=n2.id and n2.classid=t2.id and n1.testid=t3.id) as 平均分
from tbterm t1, tbclass t2 , tbtest t3,tbcourse t4 
where t1.id=t2.termid
and  t1.id=t3.termid
and  t1.id=t4.termid


termname             classname            testname             coursename           10%         10%-25%     25%-60%     60%-90%     100%        最高分                  最低分                  平均分         
-------------------- -------------------- -------------------- -------------------- ----------- ----------- ----------- ----------- ----------- -------------------- -------------------- ----------- 
一年级                  一班                   期中考试                 数学                   2           5           9           9           1           99                   12                   79
一年级                  二班                   期中考试                 数学                   1           2           6           10          0           99                   33                   70
一年级                  三班                   期中考试                 数学                   0           1           3           8           2           90                   10                   56

（所影响的行数为 3 行）

#18

路过………………