tbterm 年级表
id,termname 年级名字tbclass 班级表
id,classname 班级名字,termid 年级idtbtest 考试表
id,testname 考试名字,termidtbstudent 学生表
id,stuname 学生名字,classid 班级idtbcourse 课程表
id,coursename 课程名字,termid 年级idtbscore 分数表
id,stuid 学生id,courseid 课程id,score 分数注:写语句的时候条件可以有俩个(年级ID和科目ID)。
我想查询出全年级所有人单科成绩前10%的人数,每个班占多少人?
不算前10%的,中间的10%-25%每个班又占多少人等等...
谢谢了,本人能力不成,做不到,帮帮忙.
下边是我想得到的结果样子,如有不明白,请回复,在线等。
---------------------------------------------------------------------
班级 科目 10% 10%-25% 25%-60% 60%-90% 90%-100%
一班 数学 10 8 9 5 6
二班 数学 8 5 10 8 5
三班 数学 9 4 6 4 3
id,termname 年级名字tbclass 班级表
id,classname 班级名字,termid 年级idtbtest 考试表
id,testname 考试名字,termidtbstudent 学生表
id,stuname 学生名字,classid 班级idtbcourse 课程表
id,coursename 课程名字,termid 年级idtbscore 分数表
id,stuid 学生id,courseid 课程id,score 分数注:写语句的时候条件可以有俩个(年级ID和科目ID)。
我想查询出全年级所有人单科成绩前10%的人数,每个班占多少人?
不算前10%的,中间的10%-25%每个班又占多少人等等...
谢谢了,本人能力不成,做不到,帮帮忙.
下边是我想得到的结果样子,如有不明白,请回复,在线等。
---------------------------------------------------------------------
班级 科目 10% 10%-25% 25%-60% 60%-90% 90%-100%
一班 数学 10 8 9 5 6
二班 数学 8 5 10 8 5
三班 数学 9 4 6 4 3
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
--------创建数据库
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)数据库表结构和数据都在这了,大家帮帮忙。
班级 科目 A B C D E
一班 数学 2 2 6 3 0
二班 数学 1 1 2 3 1
三班 数学 1 1 2 3 2这样子或许简单点吧。
大致如下:
----------创建年级表
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)
goselect k1.classname , k2.coursename,
sum(case when px <= 10 then 1 else 0 end) [10%],
sum(case when px > 10 and px <= 25 then 1 else 0 end) [10%-25%],
sum(case when px > 25 and px <= 60 then 1 else 0 end) [25%-60%],
sum(case when px > 60 and px <= 90 then 1 else 0 end) [60%-90%],
sum(case when px >= 90 then 1 else 0 end) [90%-100%]
from tbclass k1 , tbcourse k2 ,
(
select t1.* , px = cast(100.0*((select count(score) from
(select m.* , n.classid from tbscore m , tbstudent n where m.stuid = n.id) t2 where t2.testid = t1.testid and t2.courseid = t1.courseid and t2.classid = t1.classid and t2.score > t1.score) + 1)
/(select count(1) from tbstudent where classid = t1.classid) as decimal(18,2))
from
(select m.* , n.classid from tbscore m , tbstudent n where m.stuid = n.id) t1
) k3
where k1.id = k3.classid and k2.id = k3.courseid
group by k1.classname , k2.coursename
order by k1.classname , k2.coursenamedrop table tbterm,tbclass,tbtest,tbstudent,tbcourse,tbscore/*
classname coursename 10% 10%-25% 25%-60% 60%-90% 90%-100%
-------------------- -------------------- ----------- ----------- ----------- ----------- -----------
二班 数学 1 1 4 3 1
三班 数学 1 1 5 2 2
一班 数学 1 1 4 3 2(所影响的行数为 3 行)
*/
例如:100 1
100 1
99 3 还是2?我是按如下的第二种方法计算名次的.
表jh03有下列数据:
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 a
ORDER BY Place
结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8
----------创建年级表
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)
goselect k1.classname , k2.coursename,
sum(case when px <= 10 then 1 else 0 end) [10%],
sum(case when px > 10 and px <= 25 then 1 else 0 end) [10%-25%],
sum(case when px > 25 and px <= 60 then 1 else 0 end) [25%-60%],
sum(case when px > 60 and px <= 90 then 1 else 0 end) [60%-90%],
sum(case when px >= 90 then 1 else 0 end) [90%-100%]
from tbclass k1 , tbcourse k2 ,
(
select t1.* , px = cast(100.0*((select COUNT(Score) from
(select m.* , n.classid from tbscore m , tbstudent n where m.stuid = n.id) t2 where t2.testid = t1.testid and t2.courseid = t1.courseid and t2.classid = t1.classid and t2.score > t1.score) + 1)
/(select count(1) from tbstudent where classid = t1.classid) as decimal(18,2))
from
(select m.* , n.classid from tbscore m , tbstudent n where m.stuid = n.id) t1
) k3
where k1.id = k3.classid and k2.id = k3.courseid
group by k1.classname , k2.coursename,k1.id
order by k1.id , k1.classname , k2.coursename
/*
classname coursename 10% 10%-25% 25%-60% 60%-90% 90%-100%
-------------------- -------------------- ----------- ----------- ----------- ----------- -----------
一班 数学 1 1 4 3 2
二班 数学 1 1 4 3 1
三班 数学 1 1 5 2 2(所影响的行数为 3 行)
*/select k1.classname , k2.coursename,
sum(case when px <= 10 then 1 else 0 end) [10%],
sum(case when px > 10 and px <= 25 then 1 else 0 end) [10%-25%],
sum(case when px > 25 and px <= 60 then 1 else 0 end) [25%-60%],
sum(case when px > 60 and px <= 90 then 1 else 0 end) [60%-90%],
sum(case when px >= 90 then 1 else 0 end) [90%-100%]
from tbclass k1 , tbcourse k2 ,
(
select t1.* , px = cast(100.0*((select COUNT(DISTINCT Score) from
(select m.* , n.classid from tbscore m , tbstudent n where m.stuid = n.id) t2 where t2.testid = t1.testid and t2.courseid = t1.courseid and t2.classid = t1.classid and t2.score >= t1.score))
/(select count(1) from tbstudent where classid = t1.classid) as decimal(18,2))
from
(select m.* , n.classid from tbscore m , tbstudent n where m.stuid = n.id) t1
) k3
where k1.id = k3.classid and k2.id = k3.courseid
group by k1.classname , k2.coursename,k1.id
order by k1.id , k1.classname , k2.coursename
/*
classname coursename 10% 10%-25% 25%-60% 60%-90% 90%-100%
-------------------- -------------------- ----------- ----------- ----------- ----------- -----------
一班 数学 1 1 6 2 0
二班 数学 1 1 4 4 1
三班 数学 1 1 5 3 1(所影响的行数为 3 行)
*/drop table tbterm,tbclass,tbtest,tbstudent,tbcourse,tbscore
sum(case when px <= 10 then 1 else 0 end) [10%],
sum(case when px > 10 and px <= 25 then 1 else 0 end) [10%-25%],
sum(case when px > 25 and px <= 60 then 1 else 0 end) [25%-60%],
sum(case when px > 60 and px <= 90 then 1 else 0 end) [60%-90%],
sum(case when px >= 90 then 1 else 0 end) [90%-100%],
max(score),
min(score),
avg(score)
from tbclass k1 , tbcourse k2 ,
(
select t1.* , px = cast(100.0*((select COUNT(DISTINCT Score) from
(select m.* , n.classid from tbscore m , tbstudent n where m.stuid = n.id) t2 where t2.testid = t1.testid and t2.courseid = t1.courseid and t2.classid = t1.classid and t2.score >= t1.score))
/(select count(1) from tbstudent where classid = t1.classid) as decimal(18,2))
from
(select m.* , n.classid from tbscore m , tbstudent n where m.stuid = n.id) t1
) k3
where k1.id = k3.classid and k2.id = k3.courseid
group by k1.classname , k2.coursename,k1.id
order by k1.id , k1.classname , k2.coursename
操作数数据类型 varchar 对于 avg 运算符无效。
with zc as(
select termname,classname,testname,coursename,score,
case
when f.score<=10 then '10%'
when f.score>10 and f.score<=25 then '10%-25%'
when f.score>25 and f.score<=60 then '25%-60%'
when f.score>60 and f.score<=90 then '60%-90%'
else '90%-100%'
end Level,
最高分=max(cast(score as numeric(5,2))) over (partition by termname,classname,testname,coursename) ,
最低分=min(cast(score as numeric(5,2))) over (partition by termname,classname,testname,coursename) ,
平均分=avg(cast(score as numeric(5,2))) over (partition by termname,classname,testname,coursename)
from tbterm a
inner join tbclass b on a.id=b.termid
inner join tbtest c on a.id=c.termid
inner join tbstudent d on b.id=d.classid
inner join tbcourse e on a.id=e.termid
inner join tbscore f on f.stuid=d.id and f.courseid=e.id and f.testid=c.id
)
select * from(
select termname,classname,testname,coursename,level,最高分,最低分,平均分 from zc
)a
pivot(
count(level) for level in([10%],[10%-25%],[25%-60%],[60%-90%],[90%-100%])
)b
order by termname,classname,testname,coursename
--结果
/*
termname classname testname coursename 最高分 最低分 平均分 10% 10%-25% 25%-60% 60%-90% 90%-100%
一年级 二班 期中考试 数学 99.00 33.00 70.700000 0 0 3 6 1
一年级 三班 期中考试 数学 90.00 10.00 56.700000 1 1 3 5 0
一年级 一班 期中考试 数学 99.00 12.00 79.700000 0 1 0 7 2
*/