-
--
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
--
方法1
select
m.C#
[
课程编号
]
, m.Cname
[
课程名称
]
,
max
(n.score)
[
最高分
]
,
min
(n.score)
[
最低分
]
,
cast
(
avg
(n.score)
as
decimal
(
18
,
2
))
[
平均分
]
,
cast
((
select
count
(
1
)
from
SC
where
C#
=
m.C#
and
score
>=
60
)
*
100.0
/
(
select
count
(
1
)
from
SC
where
C#
=
m.C#)
as
decimal
(
18
,
2
))
[
及格率(%)
]
,
cast
((
select
count
(
1
)
from
SC
where
C#
=
m.C#
and
score
>=
70
and
score
<
80
)
*
100.0
/
(
select
count
(
1
)
from
SC
where
C#
=
m.C#)
as
decimal
(
18
,
2
))
[
中等率(%)
]
,
cast
((
select
count
(
1
)
from
SC
where
C#
=
m.C#
and
score
>=
80
and
score
<
90
)
*
100.0
/
(
select
count
(
1
)
from
SC
where
C#
=
m.C#)
as
decimal
(
18
,
2
))
[
优良率(%)
]
,
cast
((
select
count
(
1
)
from
SC
where
C#
=
m.C#
and
score
>=
90
)
*
100.0
/
(
select
count
(
1
)
from
SC
where
C#
=
m.C#)
as
decimal
(
18
,
2
))
[
优秀率(%)
]
from
Course m , SC n
where
m.C#
=
n.C#
group
by
m.C# , m.Cname
order
by
m.C#
--
方法2
select
m.C#
[
课程编号
]
, m.Cname
[
课程名称
]
, (
select
max
(score)
from
SC
where
C#
=
m.C#)
[
最高分
]
, (
select
min
(score)
from
SC
where
C#
=
m.C#)
[
最低分
]
, (
select
cast
(
avg
(score)
as
decimal
(
18
,
2
))
from
SC
where
C#
=
m.C#)
[
平均分
]
,
cast
((
select
count
(
1
)
from
SC
where
C#
=
m.C#
and
score
>=
60
)
*
100.0
/
(
select
count
(
1
)
from
SC
where
C#
=
m.C#)
as
decimal
(
18
,
2
))
[
及格率(%)
]
,
cast
((
select
count
(
1
)
from
SC
where
C#
=
m.C#
and
score
>=
70
and
score
<
80
)
*
100.0
/
(
select
count
(
1
)
from
SC
where
C#
=
m.C#)
as
decimal
(
18
,
2
))
[
中等率(%)
]
,
cast
((
select
count
(
1
)
from
SC
where
C#
=
m.C#
and
score
>=
80
and
score
<
90
)
*
100.0
/
(
select
count
(
1
)
from
SC
where
C#
=
m.C#)
as
decimal
(
18
,
2
))
[
优良率(%)
]
,
cast
((
select
count
(
1
)
from
SC
where
C#
=
m.C#
and
score
>=
90
)
*
100.0
/
(
select
count
(
1
)
from
SC
where
C#
=
m.C#)
as
decimal
(
18
,
2
))
[
优秀率(%)
]
from
Course m
order
by
m.C#
--
19、按各科成绩进行排序，并显示排名
--
19.1 sql 2000用子查询完成
--
Score重复时保留名次空缺
select
t.
*
, px
=
(
select
count
(
1
)
from
SC
where
C#
=
t.C#
and
score
>
t.score)
+
1
from
sc t
order
by
t.c# , px
--
Score重复时合并名次
select
t.
*
, px
=
(
select
count
(
distinct
score)
from
SC
where
C#
=
t.C#
and
score
>=
t.score)
from
sc t
order
by
t.c# , px
--
19.2 sql 2005用rank,DENSE_RANK完成
--
Score重复时保留名次空缺(rank完成)
select
t.
*
, px
=
rank()
over
(partition
by
c#
order
by
score
desc
)
from
sc t
order
by
t.C# , px
--
Score重复时合并名次(DENSE_RANK完成)
select
t.
*
, px
=
DENSE_RANK()
over
(partition
by
c#
order
by
score
desc
)
from
sc t
order
by
t.C# , px
--
20、查询学生的总成绩并进行排名
--
20.1 查询学生的总成绩
select
m.S#
[
学生编号
]
, m.Sname
[
学生姓名
]
,
isnull
(
sum
(score),
0
)
[
总成绩
]
from
Student m
left
join
SC n
on
m.S#
=
n.S#
group
by
m.S# , m.Sname
order
by
[
总成绩
]
desc
--
20.2 查询学生的总成绩并进行排名，sql 2000用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select
t1.
*
, px
=
(
select
count
(
1
)
from
(
select
m.S#
[
学生编号
]
, m.Sname
[
学生姓名
]
,
isnull
(
sum
(score),
0
)
[
总成绩
]
from
Student m
left
join
SC n
on
m.S#
=
n.S#
group
by
m.S# , m.Sname ) t2
where
总成绩
>
t1.总成绩)
+
1
from
(
select
m.S#
[
学生编号
]
, m.Sname
[
学生姓名
]
,
isnull
(
sum
(score),
0
)
[
总成绩
]
from
Student m
left
join
SC n
on
m.S#
=
n.S#
group
by
m.S# , m.Sname ) t1
order
by
px
select
t1.
*
, px
=
(
select
count
(
distinct
总成绩)
from
(
select
m.S#
[
学生编号
]
, m.Sname
[
学生姓名
]
,
isnull
(
sum
(score),
0
)
[
总成绩
]
from
Student m
left
join
SC n
on
m.S#
=
n.S#
group
by
m.S# , m.Sname ) t2
where
总成绩
>=
t1.总成绩)
from
(
select
m.S#
[
学生编号
]
, m.Sname
[
学生姓名
]
,
isnull
(
sum
(score),
0
)
[
总成绩
]
from
Student m
left
join
SC n
on
m.S#
=
n.S#
group
by
m.S# , m.Sname ) t1
order
by
px
--
20.3 查询学生的总成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select
t.
*
, px
=
rank()
over
(
order
by
[
总成绩
]
desc
)
from
(
select
m.S#
[
学生编号
]
, m.Sname
[
学生姓名
]
,
isnull
(
sum
(score),
0
)
[
总成绩
]
from
Student m
left
join
SC n
on
m.S#
=
n.S#
group
by
m.S# , m.Sname ) t
order
by
px
select
t.
*
, px
=
DENSE_RANK()
over
(
order
by
[
总成绩
]
desc
)
from
(
select
m.S#
[
学生编号
]
, m.Sname
[
学生姓名
]
,
isnull
(
sum
(score),
0
)
[
总成绩
]
from
Student m
left
join
SC n
on
m.S#
=
n.S#
group
by
m.S# , m.Sname ) t
order
by
px
--
21、查询不同老师所教不同课程平均分从高到低显示
select
m.T# , m.Tname ,
cast
(
avg
(o.score)
as
decimal
(
18
,
2
)) avg_score
from
Teacher m , Course n , SC o
where
m.T#
=
n.T#
and
n.C#
=
o.C#
group
by
m.T# , m.Tname
order
by
avg_score
desc
--
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
--
22.1 sql 2000用子查询完成
--
Score重复时保留名次空缺
select
*
from
(
select
t.
*
, px
=
(
select
count
(
1
)
from
SC
where
C#
=
t.C#
and
score
>
t.score)
+
1
from
sc t) m
where
px
between
2
and
3
order
by
m.c# , m.px
--
Score重复时合并名次
select
*
from
(
select
t.
*
, px
=
(
select
count
(
distinct
score)
from
SC
where
C#
=
t.C#
and
score
>=
t.score)
from
sc t) m
where
px
between
2
and
3
order
by
m.c# , m.px
--
22.2 sql 2005用rank,DENSE_RANK完成
--
Score重复时保留名次空缺(rank完成)
select
*
from
(
select
t.
*
, px
=
rank()
over
(partition
by
c#
order
by
score
desc
)
from
sc t) m
where
px
between
2
and
3
order
by
m.C# , m.px
--
Score重复时合并名次(DENSE_RANK完成)
select
*
from
(
select
t.
*
, px
=
DENSE_RANK()
over
(partition
by
c#
order
by
score
desc
)
from
sc t) m
where
px
between
2
and
3
order
by
m.C# , m.px
--
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
--
23.1 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]
--
横向显示
select
Course.C#
[
课程编号
]
, Cname
as
[
课程名称
]
,
sum
(
case
when
score
>=
85
then
1
else
0
end
)
[
85-100
]
,
sum
(
case
when
score
>=
70
and
score
<
85
then
1
else
0
end
)
[
70-85
]
,
sum
(
case
when
score
>=
60
and
score
<
70
then
1
else
0
end
)
[
60-70
]
,
sum
(
case
when
score
<
60
then
1
else
0
end
)
[
0-60
]
from
sc , Course
where
SC.C#
=
Course.C#
group
by
Course.C# , Course.Cname
order
by
Course.C#
--
纵向显示1(显示存在的分数段)
select
m.C#
[
课程编号
]
, m.Cname
[
课程名称
]
, 分数段
=
(
case
when
n.score
>=
85
then
'
85-100
'
when
n.score
>=
70
and
n.score
<
85
then
'
70-85
'
when
n.score
>=
60
and
n.score
<
70
then
'
60-70
'
else
'
0-60
'
end
) ,
count
(
1
) 数量
from
Course m , sc n
where
m.C#
=
n.C#
group
by
m.C# , m.Cname , (
case
when
n.score
>=
85
then
'
85-100
'
when
n.score
>=
70
and
n.score
<
85
then
'
70-85
'
when<s
|
|
本文详细介绍了如何查询和分析各种课程的成绩数据，包括最高分、最低分、平均分、及格率、中等率、优良率、优秀率，以及学生的总成绩排名、不同老师所教课程的平均分排名，同时提供了不同技术领域的数据统计方法。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
扫一扫
