希望这个有用
http://community.csdn.net/Expert/TopicView.asp?id=5073353
http://community.csdn.net/Expert/TopicView.asp?id=5073353
解决方案 »
- float*int四舍五入
- 帮我优化一条语句
- ★★小白又来了：Sql语句，如何求最近两个月的更新的数据？
- 给错分了，30分加上。问题请见。http://community.csdn.net/Expert/topic/5157/5157729.xml?temp=.8063623
- 求一sql语句，急呀，老大们！！！！
- 求救！在win2003 web Edition下安装sql2000 安装错误！
- 更新远程数据库时出现问题,请求帮忙解决
- 为什么我 cmd可以执行单 sql的xp_cmdshell拒绝访问
- 为什么sqlserver 2000 在安装的完之后，服务器开不了？？
- 关于文件加载错误、索引与表不相匹配？
- 存储过程中的时间的问题，急，请各位高手帮忙。
- 紧急,超难的多动态表数据统计问题
if object_id('test..s_results') is not null
drop table s_results
go
create table s_results(
student_id char(10), --学生ID
class_id char(10), --班级ID
subject_id char(10), --科目ID
score decimal(18,2) --得分
)
insert into s_results(student_id,class_id,subject_id,score) values('11','01','01',11)
insert into s_results(student_id,class_id,subject_id,score) values('12','01','01',12)
insert into s_results(student_id,class_id,subject_id,score) values('13','01','01',13)
insert into s_results(student_id,class_id,subject_id,score) values('14','01','01',14)
insert into s_results(student_id,class_id,subject_id,score) values('15','01','01',15)
insert into s_results(student_id,class_id,subject_id,score) values('16','01','01',16)
insert into s_results(student_id,class_id,subject_id,score) values('11','01','02',21)
insert into s_results(student_id,class_id,subject_id,score) values('12','01','02',22)
insert into s_results(student_id,class_id,subject_id,score) values('13','01','02',23)
insert into s_results(student_id,class_id,subject_id,score) values('14','01','02',24)
insert into s_results(student_id,class_id,subject_id,score) values('15','01','02',25)
insert into s_results(student_id,class_id,subject_id,score) values('16','01','02',26)
insert into s_results(student_id,class_id,subject_id,score) values('21','02','01',31)
insert into s_results(student_id,class_id,subject_id,score) values('22','02','01',32)
insert into s_results(student_id,class_id,subject_id,score) values('23','02','01',33)
insert into s_results(student_id,class_id,subject_id,score) values('24','02','01',34)
insert into s_results(student_id,class_id,subject_id,score) values('25','02','01',35)
insert into s_results(student_id,class_id,subject_id,score) values('26','02','01',36)
insert into s_results(student_id,class_id,subject_id,score) values('21','02','02',41)
insert into s_results(student_id,class_id,subject_id,score) values('22','02','02',42)
insert into s_results(student_id,class_id,subject_id,score) values('23','02','02',43)
insert into s_results(student_id,class_id,subject_id,score) values('24','02','02',44)
insert into s_results(student_id,class_id,subject_id,score) values('25','02','02',45)
insert into s_results(student_id,class_id,subject_id,score) values('26','02','02',46)
select * from s_results
--学生基本情况表(包含学生ID，学生姓名，...其他字段我没写)
if object_id('test..s_students') is not null
drop table s_students
go
create table s_students(
student_id char(10), --学生ID
student_name char(10) --学生姓名
)
insert into s_students(student_id,student_name) values('11','张一')
insert into s_students(student_id,student_name) values('12','张二')
insert into s_students(student_id,student_name) values('13','张三')
insert into s_students(student_id,student_name) values('14','张四')
insert into s_students(student_id,student_name) values('15','张五')
insert into s_students(student_id,student_name) values('16','张六')
insert into s_students(student_id,student_name) values('21','王一')
insert into s_students(student_id,student_name) values('22','王二')
insert into s_students(student_id,student_name) values('23','王三')
insert into s_students(student_id,student_name) values('24','王四')
insert into s_students(student_id,student_name) values('25','王五')
insert into s_students(student_id,student_name) values('26','王六')
go
select * from s_students
--科目情况表(包含科目ID，科目名称)
if object_id('test..s_subjects') is not null
drop table s_subjects
go
create table s_subjects(
subject_id char(10), --科目ID
subject_name char(10) --科目名称
)
insert into s_subjects(subject_id,subject_name) values('01','语文')
insert into s_subjects(subject_id,subject_name) values('02','数学')
go
select * from s_subjects--下面是s_results数据
student_id class_id subject_id score
---------- ---------- ---------- --------------------
11 01 01 11.00
12 01 01 12.00
13 01 01 13.00
14 01 01 14.00
15 01 01 15.00
16 01 01 16.00
11 01 02 21.00
12 01 02 22.00
13 01 02 23.00
14 01 02 24.00
15 01 02 25.00
16 01 02 26.00
21 02 01 31.00
22 02 01 32.00
23 02 01 33.00
24 02 01 34.00
25 02 01 35.00
26 02 01 36.00
21 02 02 41.00
22 02 02 42.00
23 02 02 43.00
24 02 02 44.00
25 02 02 45.00
26 02 02 46.00--下面是s_students数据
student_id student_name
---------- ------------
11 张一
12 张二
13 张三
14 张四
15 张五
16 张六
21 王一
22 王二
23 王三
24 王四
25 王五
26 王六 --下面是s_subjects数据
subject_id subject_name
---------- ------------
01 语文
02 数学
select s_subjects.subject_name , s_results.class_id , s_students.student_id , s_students.student_name , s_results.score
from s_subjects , s_results , s_students
where s_subjects.subject_id = s_results.subject_id and
s_students.student_id = s_results.student_id
order by s_subjects.subject_name , s_results.class_id , s_students.student_id
/*结果
subject_name class_id student_id student_name score
------------ ---------- ---------- ------------ -----------
数学 01 11 张一 21
数学 01 12 张二 22
数学 01 13 张三 23
数学 01 14 张四 24
数学 01 15 张五 25
数学 01 16 张六 26
数学 02 21 王一 41
数学 02 22 王二 42
数学 02 23 王三 43
数学 02 24 王四 44
数学 02 25 王五 45
数学 02 26 王六 46
语文 01 11 张一 11
语文 01 12 张二 12
语文 01 13 张三 13
语文 01 14 张四 14
语文 01 15 张五 15
语文 01 16 张六 16
语文 02 21 王一 31
语文 02 22 王二 32
语文 02 23 王三 33
语文 02 24 王四 34
语文 02 25 王五 35
语文 02 26 王六 36
*/--2、统计各科目，各班总分、平均分、最高分、最低分
select s_subjects.subject_name , s_results.class_id ,
sum(s_results.score) as 总分 ,
cast(avg(s_results.score) as decimal(18,2)) as 平均分,
max(s_results.score) as 最高分 ,
min(s_results.score) as 最低分
from s_subjects , s_results , s_students
where s_subjects.subject_id = s_results.subject_id and
s_students.student_id = s_results.student_id
group by s_subjects.subject_name , s_results.class_id
order by s_subjects.subject_name , s_results.class_id
/*
subject_name class_id 总分 平均分 最高分 最低分
------------ -------- ------ ----- ------ -----
数学 01 141.00 23.50 26.00 21.00
数学 02 261.00 43.50 46.00 41.00
语文 01 81.00 13.50 16.00 11.00
语文 02 201.00 33.50 36.00 31.00
*/--3、统计各科目总分、平均分、最高分、最低分
select s_subjects.subject_name , b.总分 , b.平均分 , b.最高分 , b.最低分
from s_subjects , (
select subject_id ,
sum(score) as 总分 ,
cast(avg(score) as decimal(18,2)) as 平均分,
max(score) as 最高分 ,
min(score) as 最低分
from s_results
group by subject_id
) b
where s_subjects.subject_id = b.subject_id
order by s_subjects.subject_name
/*
subject_name 总分 平均分 最高分 最低分
------------ ------ ------ ------- --------------------
数学 402.00 33.50 46.00 21.00
语文 282.00 23.50 36.00 11.00
*/--4、统计每个学生的总成绩及在全年级的名次(倒序)
select b.student_id , a.student_name , b.成绩 into tmp
from s_students a, (
select student_id , sum(score) as 成绩
from s_results
group by student_id
) b
where a.student_id = b.student_id
SELECT * , 名次=(SELECT COUNT(成绩) FROM tmp WHERE 成绩 > a.成绩) + 1
FROM tmp a
ORDER BY 名次
drop table tmp
/*
student_id student_name 成绩 名次
---------- ------------ ------ ----
26 王六 82.00 1
25 王五 80.00 2
24 王四 78.00 3
23 王三 76.00 4
22 王二 74.00 5
21 王一 72.00 6
16 张六 42.00 7
15 张五 40.00 8
14 张四 38.00 9
13 张三 36.00 10
12 张二 34.00 11
11 张一 32.00 12
*/
select b.class_id , b.student_id , a.student_name , b.成绩 into tmp
from s_students a, (
select class_id , student_id , sum(score) as 成绩
from s_results
group by class_id , student_id
) b
where a.student_id = b.student_id
SELECT * , 名次=(SELECT COUNT(成绩) FROM tmp WHERE class_id = a.class_id and 成绩 > a.成绩) + 1
FROM tmp a
ORDER BY class_id , 名次
drop table tmp
/*
class_id student_id student_name 成绩 名次
---------- ---------- ------------ ------ ----
01 16 张六 42.00 1
01 15 张五 40.00 2
01 14 张四 38.00 3
01 13 张三 36.00 4
01 12 张二 34.00 5
01 11 张一 32.00 6
02 26 王六 82.00 1
02 25 王五 80.00 2
02 24 王四 78.00 3
02 23 王三 76.00 4
02 22 王二 74.00 5
02 21 王一 72.00 6
*/--6、统计每个学生的总成绩及在全年级的名次(倒序)
select b.subject_name , b.student_id , a.student_name , b.总成绩 into tmp
from s_students a,
(
select s_subjects.subject_name , s_results.student_id , sum(s_results.score) as 总成绩
from s_subjects , s_results
group by s_subjects.subject_name , s_results.student_id
) b
where a.student_id = b.student_id
SELECT * , 名次=(SELECT COUNT(总成绩) FROM tmp WHERE subject_name = a.subject_name and 总成绩 > a.总成绩) + 1
FROM tmp a
ORDER BY subject_name , 名次
drop table tmp
/*
subject_name student_id student_name 总成绩 名次
------------ ---------- ------------ ------ ----
数学 26 王六 82.00 1
数学 25 王五 80.00 2
数学 24 王四 78.00 3
数学 23 王三 76.00 4
数学 22 王二 74.00 5
数学 21 王一 72.00 6
数学 16 张六 42.00 7
数学 15 张五 40.00 8
数学 14 张四 38.00 9
数学 13 张三 36.00 10
数学 12 张二 34.00 11
数学 11 张一 32.00 12
语文 26 王六 82.00 1
语文 25 王五 80.00 2
语文 24 王四 78.00 3
语文 23 王三 76.00 4
语文 22 王二 74.00 5
语文 21 王一 72.00 6
语文 16 张六 42.00 7
语文 15 张五 40.00 8
语文 14 张四 38.00 9
语文 13 张三 36.00 10
语文 12 张二 34.00 11
语文 11 张一 32.00 12
*/--7、统计每个学生的每科成绩及在全年级的名次(倒序)
select s_subjects.subject_name ,
s_students.student_id ,
s_students.student_name ,
s_results.score as 成绩
into tmp
from s_subjects , s_results , s_students
where s_subjects.subject_id = s_results.subject_id and
s_students.student_id = s_results.student_id
order by s_subjects.subject_name , s_students.student_idSELECT * , 名次=(SELECT COUNT(成绩) FROM tmp WHERE subject_name = a.subject_name and 成绩 > a.成绩) + 1
into tmp2
FROM tmp a
ORDER BY subject_name , 名次declare @sql varchar(8000)
set @sql = 'select student_id , student_name'
select @sql = @sql + ' , sum(case subject_name when ''' + subject_name + ''' then 成绩 end) [' + rtrim(subject_name) + '得分' + ']' +
' , sum(case subject_name when ''' + subject_name + ''' then 名次 end) [' + '全年级名次' + ']'
from (select distinct subject_name from tmp2) as a
set @sql = @sql + ' from tmp2 group by student_id , student_name order by student_id'
exec(@sql) drop table tmp
drop table tmp2
/*
student_id student_name 数学得分 全年级名次 语文得分 全年级名次
---------- ------------ --------- ----------- --------- -----------
11 张一 21.00 12 11.00 12
12 张二 22.00 11 12.00 11
13 张三 23.00 10 13.00 10
14 张四 24.00 9 14.00 9
15 张五 25.00 8 15.00 8
16 张六 26.00 7 16.00 7
21 王一 41.00 6 31.00 6
22 王二 42.00 5 32.00 5
23 王三 43.00 4 33.00 4
24 王四 44.00 3 34.00 3
25 王五 45.00 2 35.00 2
26 王六 46.00 1 36.00 1
*/
select b.student_id , a.student_name , b.总成绩 into tmp
from s_students a,
(
select s_results.student_id , sum(s_results.score) as 总成绩
from s_subjects , s_results
group by s_results.student_id
) b
where a.student_id = b.student_id
SELECT * , 名次=(SELECT COUNT(总成绩) FROM tmp WHERE 总成绩 > a.总成绩) + 1
FROM tmp a
ORDER BY 名次
drop table tmp
/*
student_id student_name 总成绩 名次
---------- ------------ ------- -----------
26 王六 164.00 1
25 王五 160.00 2
24 王四 156.00 3
23 王三 152.00 4
22 王二 148.00 5
21 王一 144.00 6
16 张六 84.00 7
15 张五 80.00 8
14 张四 76.00 9
13 张三 72.00 10
12 张二 68.00 11
11 张一 64.00 12
*/