数据库实战完全笔记综合练习（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 FRIDAY
from 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) FRIDAY
from SCHEDULE
group 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  FRIDAY
from SCHEDULE
group by teacherid;

查询结果如下图：