資料庫實戰完全筆記綜合練習（1）授課資訊查詢，綜合練習資訊查詢

資料庫實戰完全筆記綜合練習（1）授課資訊查詢，綜合練習資訊查詢

    此執行個體在Oracle10g運行通過：

一、題目要求

教師的授課資訊存放在SCHEDULE表中 ：

--------------------------------
TEACHERID    WEEKDAY  HASCOURSE
---------- ---------- ----------
     1                                 2          1
     1                                 3          1
     2                                 1          1
     3                                 2          1
     1                                 2          1
--------------------------------
-----------------------------------------------------------------
設計查詢,輸出如下資訊(星期下數表示:對應教師星期幾排的課數):
TEACHERID     MONDAY    TUESDAY  WEDNESDAY   THURSDAY     FRIDAY
---------- ---------- ---------- ---------- ---------- ----------
     1                          0                 2                    1                     0                         0
     2                          1                 0                     0                    0                         0
     3                          0                 1                     0                    0                         0

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

二、分析解答

1.建表語句 

create table schedule(teacherid number(1),weekday number(1),hascourse number(1));--插入資料語句insert into schedule values(1,2,1);insert into schedule values(1,3,1);insert into schedule values(2,1,2);insert into schedule values(3,2,1);insert into schedule values(1,2,1);--提交語句commit; 

2.查詢語句
第一步，得到如下結果：
TEACHERID     MONDAY    TUESDAY  WEDNESDAY   THURSDAY     FRIDAY
--------- ---------- ---------- ---------- ---------- ----------
        1          0          1          0          0          0
        1          0          0          1          0          0
        2          1          0          0          0          0
        3          0          1          0          0          0
        1          0          1          0          0          0
查詢語句為：

select  teacherid,case when  weekday=1 then 1 else 0 end MONDAY,case when  weekday=2 then 1 else 0 end TUESDAY, case when  weekday=3 then 1 else 0 end WEDNESDAY,case when  weekday=4 then 1 else 0 end THURSDAY,case when  weekday=5 then 1 else 0 end FRIDAYfrom SCHEDULE;

第二步，將第一步的結果分組求和，得到最終結果：

select  teacherid,sum(case when  weekday=1 then 1 else 0 end) MONDAY,sum(case when  weekday=2 then 1 else 0 end) TUESDAY,sum(case when  weekday=3 then 1 else 0 end) WEDNESDAY,sum(case when  weekday=4 then 1 else 0 end) THURSDAY,sum(case when  weekday=5 then 1 else 0 end) FRIDAYfrom SCHEDULEgroup by teacherID;

如果資料中含有 null，可以用下面的語句，調用nvl函數，語句如下：

select teacherid,nvl ((SUM(decode(weekday, '1', 1))),0) as  MONDAY,nvl ((SUM(decode(weekday, '2', 1))),0) as  TUESDAY,nvl ((SUM(decode(weekday, '3', 1))),0) as  WEDNESDAY,nvl ((SUM(decode(weekday, '4', 1))),0) as  THURSDAY,nvl ((SUM(decode(weekday, '5', 1))),0) as  FRIDAYfrom SCHEDULEgroup by teacherid;

查詢結果如：