--
新生高考成绩按专业分布统计
select
top
1000
MajorName,MajorNameBrief,
[
ChineseScore50-60
]
,
[
ChineseScore61-70
]
,
[
ChineseScore71-80
]
,
[
ChineseScore81-90
]
,
[
ChineseScore90以上
]
,
[
MathScore50-60
]
,
[
MathScore61-70
]
,
[
MathScore71-80
]
,
[
MathScore81-90
]
,
[
MathScore90以上
]
,
[
ForeignLangScore50-60
]
,
[
ForeignLangScore61-70
]
,
[
ForeignLangScore71-80
]
,
[
ForeignLangScore81-90
]
,
[
ForeignLangScore90以上
]
,
[
PhysicsScore50-60
]
,
[
PhysicsScore61-70
]
,
[
PhysicsScore71-80
]
,
[
PhysicsScore81-90
]
,
[
PhysicsScore90以上
]
from
(
select
distinct
MajorNo,
--
高考语文成绩分布
count
(
case
when
ChineseScore
>=
50
and
ChineseScore
<=
60
then
ChineseScore
end
)
as
'
ChineseScore50-60
'
,
count
(
case
when
ChineseScore
>=
61
and
ChineseScore
<=
70
then
ChineseScore
end
)
as
'
ChineseScore61-70
'
,
count
(
case
when
ChineseScore
>=
71
and
ChineseScore
<=
80
then
ChineseScore
end
)
as
'
ChineseScore71-80
'
,
count
(
case
when
ChineseScore
>=
81
and
ChineseScore
<
90
then
ChineseScore
end
)
as
'
ChineseScore81-90
'
,
count
(
case
when
ChineseScore
>=
90
then
ChineseScore
end
)
as
'
ChineseScore90以上
'
,
--
高考数学成绩分布
count
(
case
when
MathScore
>=
50
and
MathScore
<=
60
then
MathScore
end
)
as
'
MathScore50-60
'
,
count
(
case
when
MathScore
>=
61
and
MathScore
<=
70
then
MathScore
end
)
as
'
MathScore61-70
'
,
count
(
case
when
MathScore
>=
71
and
MathScore
<=
80
then
MathScore
end
)
as
'
MathScore71-80
'
,
count
(
case
when
MathScore
>=
81
and
MathScore
<
90
then
MathScore
end
)
as
'
MathScore81-90
'
,
count
(
case
when
MathScore
>=
90
then
MathScore
end
)
as
'
MathScore90以上
'
,
--
高考外语成绩分布
count
(
case
when
ChineseScore
>=
50
and
ForeignLangScore
<=
60
then
ForeignLangScore
end
)
as
'
ForeignLangScore50-60
'
,
count
(
case
when
ForeignLangScore
>=
61
and
ForeignLangScore
<=
70
then
ForeignLangScore
end
)
as
'
ForeignLangScore61-70
'
,
count
(
case
when
ForeignLangScore
>=
71
and
ForeignLangScore
<=
80
then
ForeignLangScore
end
)
as
'
ForeignLangScore71-80
'
,
count
(
case
when
ForeignLangScore
>=
81
and
ForeignLangScore
<
90
then
ForeignLangScore
end
)
as
'
ForeignLangScore81-90
'
,
count
(
case
when
ForeignLangScore
>=
90
then
ForeignLangScore
end
)
as
'
ForeignLangScore90以上
'
,
--
高考综合成绩分布, 以物理成绩字段为综合
count
(
case
when
PhysicsScore
>=
50
and
PhysicsScore
<=
60
then
PhysicsScore
end
)
as
'
PhysicsScore50-60
'
,
count
(
case
when
PhysicsScore
>=
61
and
PhysicsScore
<=
70
then
PhysicsScore
end
)
as
'
PhysicsScore61-70
'
,
count
(
case
when
PhysicsScore
>=
71
and
PhysicsScore
<=
80
then
PhysicsScore
end
)
as
'
PhysicsScore71-80
'
,
count
(
case
when
PhysicsScore
>=
81
and
PhysicsScore
<
90
then
PhysicsScore
end
)
as
'
PhysicsScore81-90
'
,
count
(
case
when
PhysicsScore
>=
90
then
PhysicsScore
end
)
as
'
PhysicsScore90以上
'
from
StuInfoFresh
group
by
MajorNo)
as
vwAll
left
join
CdMajor
on
vwAll.MajorNo
=
CdMajor.MajorNo
order
by
CdMajor.MajorNo
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
