統計出每個教師每門課的及格人數和及格率
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%

如果還有更好的寫法請列出來謝謝

一道sql面試題的求解方法

相關文章