只有一个student表四个字段没有主键
班级 学号 课程 成绩
class user_id kecheng chengji问题是:
求每一个班级每一门课程的及格率
刚发快了,忘了给分,再发!
班级 学号 课程 成绩
class user_id kecheng chengji问题是:
求每一个班级每一门课程的及格率
刚发快了,忘了给分,再发!
解决方案 »
- ORACLE数据库挂接问题
- 关于触发器(删除表)的问题
- 过程 结果集 几个问题!?
- 这样一个存储过程怎么调用呀?
- 为什么不能安装ORACLE
- Oracle beginner,first time ask a question,see what's the problem,why?
- 请教一个数据库查询问题!
- 如何把字符串"6/20/01 4:56:16 PM"转换成为Oracle的日期型?jiezhi(Niu)大哥在么?
- 急!ASP.net连接Oracle9I的问题
- ORACLE中的主机字符串真讨厌, 不知道干什么用的!
- Oracle10G RAC specify network interface usage识别不到系统网卡信息,急急急急
- 100分只求只有一个表的SQL语句
FROM STUDENT T,(SELECT COUNT(*) AS TOTAL FROM STUDENT) B
GROUP BY CLASS,KECHENG
HAVING T.CHENGJI >=60
---------- ---------- ---------- ----------
1 1 语文 80
1 2 语文 90
2 1 数学 90
2 2 语文 100
1 4 数学 87SQL> select class,subject,sum(score) score,count(*) num, sum(score)/count(*) pingjun from zzw_test342
2 group by class,subject; CLASS SUBJECT SCORE NUM PINGJUN
---------- ---------- ---------- ---------- ----------
1 数学 87 1 87
1 语文 170 2 85
2 数学 90 1 90
2 语文 100 1 100SQL>
FROM (SELECT class, kecheng,
(SELECT COUNT(kecheng) AS Expr1
FROM student AS a
WHERE (class = student.class) AND (kecheng = student.kecheng) AND (chengji >= 60)) AS jgrs,
(SELECT COUNT(kecheng) AS Expr1
FROM student AS b
WHERE (class = student.class) AND (kecheng = student.kecheng)) AS qbrs
FROM student
GROUP BY kecheng, class, chengji) AS derivedtbl_1
GROUP BY class, kecheng, jgrs, qbrs1.00 为及格率为100%,
0。5为50%
0,为没有及格
class,
kecheng,
round(sum(case when chengji < 60 then 0 else 1 end)/count(*),3)*100||'%' as jigelv
from tt
group by class,kecheng;
下面是测试:[SYS@ora10gr1#03-12月-09] SQL>with tt as(
2 select 'c001' class,'c001u001' user_id,'语文' kecheng, 60 chengji from dual
3 union all
4 select 'c001' class,'c001u002' user_id,'语文' kecheng, 59 chengji from dual
5 union all
6 select 'c001' class,'c001u003' user_id,'语文' kecheng, 52 chengji from dual
7 union all
8 select 'c001' class,'c001u001' user_id,'数学' kecheng, 60 chengji from dual
9 union all
10 select 'c001' class,'c001u002' user_id,'数学' kecheng, 80 chengji from dual
11 union all
12 select 'c001' class,'c001u003' user_id,'数学' kecheng, 52 chengji from dual
13 union all
14 select 'c002' class,'c002u001' user_id,'语文' kecheng, 70 chengji from dual
15 union all
16 select 'c002' class,'c002u002' user_id,'语文' kecheng, 90 chengji from dual
17 union all
18 select 'c002' class,'c002u003' user_id,'语文' kecheng, 52 chengji from dual
19 union all
20 select 'c002' class,'c002u001' user_id,'数学' kecheng, 60 chengji from dual
21 union all
22 select 'c002' class,'c002u002' user_id,'数学' kecheng, 65 chengji from dual
23 union all
24 select 'c002' class,'c002u003' user_id,'数学' kecheng, 88 chengji from dual
25 )
26 select
27 class,
28 kecheng,
29 round(sum(case when chengji < 60 then 0 else 1 end)/count(*),3)*100||'%' as jigelv
30 from tt
31 group by class,kecheng;CLAS KECH JIGELV
---- ---- -------
c001 数学 66.7%
c001 语文 33.3%
c002 数学 100%
c002 语文 66.7%
count(decode(sign(chengji-60),1,1))/count(1) 及格率
from student
group by class,kecheng
order by 1,2
kecheng,
to_char(SUM(decode(sign(score - 60), -1, 0, 1)) /
(SELECT COUNT(1) FROM tab_test b WHERE a.cla = b.cla) * 100)||'%'
FROM tab_test a
GROUP BY cla, kecheng
ORDER BY cla,kecheng;经过测试数据还是正确的!希望提出宝贵意见。
学习了,但是有个小错误 成绩等于 60 的时候得不到正确结果。select class,kecheng,
count(decode(sign(chengji-59),1,1))/count(1) 及格率
from student
group by class,kecheng
order by 1,2
count(decode(sign(chengji-60),-1,null,1))/count(1) 及格率
from student
group by class,kecheng
order by 1,2
sum(case when chengji<60 then 0 else 1 end)/count(1)
from student
group by class,kecheng
order by 1,2
group by class,kecheng
, a.kecheng
, round(a.count/b.count) as jigelv
from (select class
, kecheng
, chengji
from student
where chengji>=60
group by class, kecheng
) as a
,(select class
, kecheng
, chengji
from student
group by class, kecheng
) as b
where a.class=b.class
and a.kecheng=b.kecheng
select a.class
, a.kecheng
, round(jigeshu/zhongshu) as jigelv
from (select class
, kecheng
, count(*) as jigeshu
from student
where chengji>=60
group by class, kecheng
) as a
,(select class
, kecheng
, count(*) as zhongshu
from student
group by class, kecheng
) as b
where a.class=b.class
and a.kecheng=b.kecheng
count(decode(sign(chengji-60),-1,null,1))/count(1) 及格率
from student
group by class,kecheng
order by 1,2 这个最佳
select class,kecheng,sum(case when chengji>=60 then 1 else 0 end )/count(1) from student
group by class,kecheng
这个最容易理解!