# 献上100分数，求一个sql语句，各位高手帮帮忙！

zyd_fyl 2010-04-15 10:01:25
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 分数

---------------------------------------------------------------------

...全文
159 22 1 打赏 收藏 举报

22 条回复

zyd_fyl 2010-04-16

• 打赏
• 举报

--小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函数啊 这个你都不会??
• 打赏
• 举报

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%
-------------------- -------------------- ----------- ----------- ----------- ----------- -----------

（所影响的行数为 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%
-------------------- -------------------- ----------- ----------- ----------- ----------- -----------

（所影响的行数为 3 行）
*/

drop table tbterm,tbclass,tbtest,tbstudent,tbcourse,tbscore

``````

• 打赏
• 举报

zyd_fyl 2010-04-15

• 打赏
• 举报

dawugui 2010-04-15
[Quote=引用 12 楼 zyd_fyl 的回复:]

[/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        8``````
• 打赏
• 举报

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
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%
-------------------- -------------------- ----------- ----------- ----------- ----------- -----------

（所影响的行数为 3 行）
*/``````
• 打赏
• 举报

zyd_fyl 2010-04-15

• 打赏
• 举报

artoksxb 2010-04-15

• 打赏
• 举报

--小F-- 2010-04-15

• 打赏
• 举报

zyd_fyl 2010-04-15
``````

``````

• 打赏
• 举报

zyd_fyl 2010-04-15
``````

``````

• 打赏
• 举报

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-- 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%

*/``````
• 打赏
• 举报

2.1w+

MS-SQL Server 疑难问题

2010-04-15 10:01