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

zyd_fyl 2010-04-16 10:49:05 


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=''



不理解的回复，本人在线等。
...全文
290 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
Ghost_Khz 2010-04-17
  • 打赏
  • 举报
 回复
路过………………
喜-喜 2010-04-16
  • 打赏
  • 举报
 回复 
--准备工作

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



*/
zyd_fyl 2010-04-16
  • 打赏
  • 举报
 回复
哦，好了，我看下结果。
zyd_fyl 2010-04-16
  • 打赏
  • 举报
 回复
东升哥，你这个报
消息 512，级别 16，状态 1，第 7 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
这样子的错误，小弟实在笨的很，所以。。。
东那个升 2010-04-16
  • 打赏
  • 举报
 回复 


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 行受影响)
htl258_Tony 2010-04-16
  • 打赏
  • 举报
 回复
可惜，那天做上午做一半半，是照TOP n PENCENT来做的，下午回来后发现结贴了，以为我的想法错了，代码全关掉了。
zyd_fyl 2010-04-16
  • 打赏
  • 举报
 回复
嗯，呵呵。
--小F-- 2010-04-16
  • 打赏
  • 举报
 回复
那天的有问题??
永生天地 2010-04-16
  • 打赏
  • 举报
 回复 


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 行）
zyd_fyl 2010-04-16
  • 打赏
  • 举报
 回复
把100分成5份，0-10，10-25，25-60，60-90，90-10.
上边就相当于百分比。
在每个百分比当中，每个班占多少人？
比如说10-25这个数值之间每个班占了几个人。
例：一个年级有100个人，按单科分数排名，前10名中每个班占几个人，第10到25名中，每个班占多少人，
第25-60名中，每个班占多少人？
东那个升 2010-04-16
  • 打赏
  • 举报
 回复
还有百分比的算法。举个例子
东那个升 2010-04-16
  • 打赏
  • 举报
 回复 
去掉前10%的人,中间的10%-25%每个班又占多少人，去掉前25%，中间25%-60%每个班又占多少人等...

就是把100%拆分开，中间的10%-25%这一段的人数每个班占多少人！


不懂
zyd_fyl 2010-04-16
  • 打赏
  • 举报
 回复
不对，不是多少分到多少分。
ws_hgo 2010-04-16
  • 打赏
  • 举报
 回复
不是写给了的马？？
喜-喜 2010-04-16
  • 打赏
  • 举报
 回复
关注...
fyl_zyd 2010-04-16
  • 打赏
  • 举报
 回复
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 行）
jwwyqs 2010-04-16
  • 打赏
  • 举报
 回复
[Quote=引用 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……
[/Quote]
强人啊
喜-喜 2010-04-16
  • 打赏
  • 举报
 回复 
--准备工作

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



*/


对结果排序，看起来更舒服些....
基础类 - SQL语句
基础类 - SQL语句 <br />转载请标明出处： http://blog.csdn.net/hz/archive/2006/03/01/613376.asp 本文来自 CSDN 博客。x sql server 怎么跨库查询？ 如何判断字符的大小写 ? 如何...
MS-SQL Server 基础类 - SQL语句
一个高难度的MS SQL问题请教各位高手！ 这样的sql怎么写啊?......................急的很哩!! 求教sql中要读注册表信息用什么系统过程 请问一下在SQLSERVER中把一个表的一个字段的值附加到另一个表这种情况怎样解决...
SSIS最佳实践：SQL Server提升执行性能
SQL Server集成服务(SQL Server Integration Services，SSIS)在其前辈DTS(Data Transformation Services，数据转换服务)的基础上进步了不少，从可用性、性能和并行等方面来说，它已经成长为一个企业级ETL(Extraction...
SQL Server 分区表 处理海量数据
你是不是也在苦苦寻求优化自己的SQL Server数据库的方法?如果你的数据库里有不少非常大的表格，分区功能能够帮到你很大的忙…… 【IT专家网独家】你是不是也在苦苦寻求优化自己的SQL Server数据库的...
vb/vb.net开发技巧荟萃(六)
300元请人做一个自动点击领取的软件 ...(100分求) SQL查询问题??会的快来 已会C++, 再学习VB还是C#? 如何在用一个程序打开一个文件的同时,用另一程序获取该文件的文件名? VB中如何用ADO从ORACLE中复制一个表到AC...
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章