统计出每个教师每门课的及格人数和及格率
create table rock (教师ID number,学生ID number,学科名称 varchar2(40),成绩 number);

insert into rock values(1,1,'数学',80);
insert into rock values(1,2,'数学',50);
insert into rock values(2,3,'英语',61);
insert into rock values(2,4,'英语',59);
insert into rock values(3,5,'语文',62);
insert into rock values(3,6,'语文',58);
insert into rock values(1,7,'数学',81);
commit;

SQL> select * from rock;

      教师ID       学生ID 学科名称                                         成绩
---------- ---------- ---------------------------------------- ----------
         1          1 数学                                             80
         1          2 数学                                             50
         2          3 英语                                             61
         2          4 英语                                             59
         3          5 语文                                             62
         3          6 语文                                             58
         1          7 数学                                             81

7 rows selected

第一种写法

PHP code:



SQL> select a.教师ID,


  2         a.学科名称,


  3         a.及格人数,


  4         round( a.及格人数 / 总人数 *100) ||'%' as 及格率


  5    from (select 教师ID, 学科名称, count(*) as 及格人数


  6            from rock


  7           where 成绩 >= 60


  8           group by 教师ID, 学科名称) a,


  9         (select 教师ID, 学科名称, count(*) as 总人数


 10            from rock


 11           group by 教师ID, 学科名称) b


 12   where a.教师ID = b.教师ID


 13     and a.学科名称 = b.学科名称


 14  ;


 


      教师ID 学科名称                                       及格人数 及格率


---------- ---------------------------------------- ---------- -----------------------------------------


         1 数学                                              2 67%


         2 英语                                              1 50%


         3 语文                                              1 50%

第二种写法: 用count()分析函数

PHP code:
SQL> select a.教师ID,

  2         a.学科名称,

  3         a.及格人数,

  4         round( a.及格人数 / 总人数 *100) ||'%' as 及格率

  5     from

  6  (select distinct 教师ID, 学科名称,count(学生ID) over(partition by 教师ID, 学科名称 order by 教师ID) 及格人数

  7      from rock

  8    where 成绩>=60) a,

  9  (select distinct 教师ID, 学科名称,count(学生ID) over(partition by 教师ID, 学科名称 order by 教师ID) 总人数

10      from rock) b

11  where a.教师ID = b.教师ID

12     and a.学科名称 = b.学科名称

13  ;

      教师ID 学科名称                                       及格人数及格率

---------- ---------------------------------------- ---------- -----------------------------------------

         1 数学                                              2 67%

         2 英语                                              1 50%

         3 语文                                              1 50%

第三种写法: with写法

SQL>
SQL> WITH A AS (select 教师ID,学科名称,COUNT(教师ID) 及格人數
2                 FROM ROCK
3                  WHERE 成绩>=60
4                   GROUP BY 教师ID,学科名称),
5         B AS (SELECT 教师ID,学科名称,COUNT(学科名称) 人數 FROM ROCK
6               GROUP BY 教师ID,学科名称
7              ORDER BY 教师ID,学科名称)
8    select A.*,ROUND(A.及格人數/B.人數*100,2)||'%' 及格率 FROM A,B
9                WHERE A.教师ID=B.教师ID AND A.学科名称=B.学科名称
10 ;

      教师ID 学科名称                                       及格人數及格率
---------- ---------------------------------------- ---------- -----------------------------------------
         1 数学                                              2 66.67%
         2 英语                                              1 50%
         3 语文                                              1 50%

如果还有更好的写法请列出来谢谢

ITPub博客

一道sql面试题的求解方法

最新文章