22,207
社区成员
发帖
与我相关
我的任务
分享献上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，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 年级id
tbtest 考试表
id,testname 考试名字，termid
tbstudent 学生表
id,stuname 学生名字，classid 班级id
tbcourse 课程表
id，coursename 课程名字，termid 年级id
tbscore 分数表
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
...全文
请发表友善的回复…
发表回复
zyd_fyl 2010-04-16
- 打赏
- 举报
呵呵，朋友，错了啊，那个10%和25%什么的，就是把100%拆分开，年级成绩前10%的人数，每个班占多少人，
去掉这个10%，中间的10%-25%，每个班又占多少人？如果能改下最好帮帮忙。谢谢。
去掉这个10%，中间的10%-25%，每个班又占多少人？如果能改下最好帮帮忙。谢谢。
--小F-- 2010-04-15
- 打赏
- 举报
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%],
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--小F-- 2010-04-15
- 打赏
- 举报
[Quote=引用 16 楼 zyd_fyl 的回复:]
最好是在加三列，最高分，最低分，平局分。我给你加分。谢谢，谢谢！
[/Quote]
max,min,avg函数啊 这个你都不会??
最好是在加三列，最高分，最低分，平局分。我给你加分。谢谢，谢谢！
[/Quote]
max,min,avg函数啊 这个你都不会??
zyd_fyl 2010-04-15
- 打赏
- 举报
最好是在加三列，最高分，最低分，平局分。我给你加分。谢谢，谢谢！
dawugui 2010-04-15
- 打赏
- 举报
以下是名次的两种算法.加上排序,不过貌似不管哪种算法和你的结果都有出入,你参考后可自行修改.
----------创建年级表
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)
go
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(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
zyd_fyl 2010-04-15
- 打赏
- 举报
哦，好，那你帮忙把班级按班级ID排序下，就OK了。谢谢。分给你。
dawugui 2010-04-15
- 打赏
- 举报
[Quote=引用 12 楼 zyd_fyl 的回复:]
人数不对啊，90%-100%应该只有三个人的啊，我想应该是转换的问题吧。那就是这样喽，根据班级ID排序，我看都看不懂，麻烦帮小小的改动下，谢谢。
[/Quote]
这要看你怎么算了,你同班同科目存在相同分数,那么名次改怎么算?.你再仔细看看你的数据.
例如:
100 1
100 1
99 3 还是2?
我是按如下的第二种方法计算名次的.
人数不对啊，90%-100%应该只有三个人的啊，我想应该是转换的问题吧。那就是这样喽，根据班级ID排序，我看都看不懂，麻烦帮小小的改动下，谢谢。
[/Quote]
这要看你怎么算了,你同班同科目存在相同分数,那么名次改怎么算?.你再仔细看看你的数据.
例如:
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 50
1. 名次生成方式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 6
2. 名次生成方式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 8zyd_fyl 2010-04-15
- 打赏
- 举报
人数不对啊，90%-100%应该只有三个人的啊，我想应该是转换的问题吧。那就是这样喽，根据班级ID排序，我看都看不懂，麻烦帮小小的改动下，谢谢。
dawugui 2010-04-15
- 打赏
- 举报
就是行列转换吧?你具体的那些关系,我没看明白,如果需要,自己再加上即可.
大致如下:
大致如下:
----------创建年级表
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)
go
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(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.coursename
drop 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 行）
*/zyd_fyl 2010-04-15
- 打赏
- 举报
最好是能帮忙做出来，我做不到哦。
artoksxb 2010-04-15
- 打赏
- 举报
先统计行，然后行转列
--小F-- 2010-04-15
- 打赏
- 举报
先统计每一样的 然后再行转列...
zyd_fyl 2010-04-15
- 打赏
- 举报
班级 科目 A B C D E
一班 数学 2 2 6 3 0
二班 数学 1 1 2 3 1
三班 数学 1 1 2 3 2
这样子或许简单点吧。
zyd_fyl 2010-04-15
- 打赏
- 举报
班级 科目 10% 10%-25% 25%-60% 60%-90% 90%-100%
一班 数学 2 2 6 3 0
二班 数学 1 1 2 3 1
三班 数学 1 1 2 3 2
我想要这样子的一个结果，谢谢。
zyd_fyl 2010-04-15
- 打赏
- 举报
--------创建数据库
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)
数据库表结构和数据都在这了，大家帮帮忙。
dawugui 2010-04-15
- 打赏
- 举报
最好给出完整的表结构,测试数据,计算方法和正确结果.发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
zyd_fyl 2010-04-15
- 打赏
- 举报
呵呵，上次那个我也没改出来，不对哦，但谢谢你帮忙。谢谢。小F。
--小F-- 2010-04-15
- 打赏
- 举报
就是在我那天写的基础上来个行转列..
htl258_Tony 2010-04-15
- 打赏
- 举报
没有数据，有结果？帮顶了
zc_0101 2010-04-15
- 打赏
- 举报
--查询
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
*/加载更多回复（2）
