一个分数统计的问题 查出及格 不及格 和及格率

[JeffreyZhang1314]

先说需求，我要计算出某个班级在一次考试中每个科目考试的及格情况，语文及格人数 不及格人数 及格率（及格人数/总人数） 我希望可以在数据库中用SQL语句直接得到结果，其中及格分数自己来确定。样子如下

科目 及格 不及格 及格率
语文 20 5 80%(20/25)
数学 22 3 88%(22/25)
英语 20 0 100%(20/20 数据库中只有20个学生有英语成绩)

我数据库表的结构如下面描述

学生编号 分数 科目
bob1 32 1（语文）
bob2 85 1（语文）
bob2 80 2（数学）
bob1 65 2（数学）
bob1 90 3（英语）
bob3 75 1（语文）

不用考虑多少个人没有参加考试，只要计算出每个科目有成绩的总人 不及格人数 及格人数 这样就OK了 。
请各位高手帮忙如果这样设计不合理请指点一下数据库要如何设计。拜托了！谢谢！

[华夏小卒]

--> 测试数据: #stu
if object_id('tempdb.dbo.#stu') is not null drop table #stu
go
create table #stu (学生编号 varchar(4),分数 int,科目 varchar(4))
insert into #stu
select 'bob1',32,'语文' union all
select 'bob2',85,'语文' union all
select 'bob2',80,'数学' union all
select 'bob1',65,'数学' union all
select 'bob1',90,'英语' union all
select 'bob3',75,'语文'


declare @yw int,@sx int,@yy int
set @yw=50
set @yy=60
set @sx=70

select *,不及格=总人-及格,及格率=ltrim(cast(及格*100.0/总人 as decimal(18,1)))+'%'
from 
(
	select 科目,
		总人=count(*),
		及格=sum(case when 分数>=(case 科目 when '语文' then @yw 
										when '数学' then @sx 
										when '英语' then @yy end) 
					 then 1 else 0 end) 
	from #stu
	group by 科目
)t


科目   总人          及格          不及格         及格率
---- ----------- ----------- ----------- ------------------------------------------
数学   2           1           1           50.0%
英语   1           1           0           100.0%
语文   3           2           1           66.7%

(3 行受影响)

[JeffreyZhang1314]

哥们你的回帖速度之快 真的是非常的感谢你！你太优秀了！你是我的偶像了

[dawugui]

create table tb (学生编号 varchar(4),分数 int,科目 varchar(4))
insert into tb
select 'bob1',32,'语文' union all
select 'bob2',85,'语文' union all
select 'bob2',80,'数学' union all
select 'bob1',65,'数学' union all
select 'bob1',90,'英语' union all
select 'bob3',75,'语文'
go
--按照60分为及格。
select 科目,
       及格 = (select count(1) from tb where 分数 >= 60 and 科目 = t.科目) ,
       不及格 = (select count(1) from tb where 分数 < 60 and 科目 = t.科目), 
       及格率 = cast(100.0*(select count(1) from tb where 分数 >= 60 and 科目 = t.科目)/(select count(1) from tb where 科目 = t.科目) as decimal(18,2))
from tb t
group by t.科目
/*
科目   及格          不及格         及格率                  
---- ----------- ----------- -------------------- 
数学   2           0           100.00
英语   1           0           100.00
语文   2           1           66.67

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

--自定义及格分数
declare @score as int
set @score = 60
select 科目,
       及格 = (select count(1) from tb where 分数 >= @score and 科目 = t.科目) ,
       不及格 = (select count(1) from tb where 分数 < @score and 科目 = t.科目), 
       及格率 = cast(100.0*(select count(1) from tb where 分数 >= @score and 科目 = t.科目)/(select count(1) from tb where 科目 = t.科目) as decimal(18,2))
from tb t
group by t.科目
/*
科目   及格          不及格         及格率                  
---- ----------- ----------- -------------------- 
数学   2           0           100.00
英语   1           0           100.00
语文   2           1           66.67

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

drop table tb

[AcHerat]

select *,ltrim((cast([及格人数] as int)/(cast([及格人数] as int)+cast([不及格人数] as int))))+'%' as [及格率]
from(
    (select [科目],count([分数]) as [及格人数]
    from [成绩表]
    where [分数] >= 2)a
    join
    (select [科目],count([分数]) as [不及格人数]
    from [成绩表]
    where [分数] < 2)b
    on a.[科目] = b.[科目]
)U

[飘零一叶]

if object_id('tempdb.dbo.#stu') is not null drop table #stu
go
create table #stu (学生编号 varchar(4),分数 int,科目 varchar(4))
insert into #stu
select 'bob1',32,'语文' union all
select 'bob2',85,'语文' union all
select 'bob2',80,'数学' union all
select 'bob1',65,'数学' union all
select 'bob1',90,'英语' union all
select 'bob3',75,'语文'


select 科目
,sum(case when 分数>=60 then 1 else 0 end) as 及格
,sum(case when 分数<60 then 1 else 0 end) as 不及格
,1.*sum(case when 分数>=60 then 1 else 0 end)/COUNT(学生编号)*100 as 及格率
from #stu group by 科目
------------------------
科目	及格	不及格	及格率
数学	2	0	100.00000000000
英语	1	0	100.00000000000
语文	2	1	66.66666666600

[AcHerat]

[Quote=引用 4 楼 acherat 的回复:]
SQL code

select *,ltrim((cast([及格人数] as int)/(cast([及格人数] as int)+cast([不及格人数] as int))))+'%' as [及格率]
from(
(select [科目],count([分数]) as [及格人数]
from [成绩表]
where [分数] >= 2)a
joi……
[/Quote]

第一句有点错！

select *,ltrim((cast([及格人数] as decimal(18,2))*100/(cast([及格人数] as decimal(18,2))+cast([不及格人数] as decimal(18,2)))))+'%' as [及格率]