PL/SQL编程之道之CASE使用(22)

9 教师
有一张反映任课教师的报表
由于考虑到一个课程可能有多个任课教师
所以报表上有两个位置来显示教师的名字
规则如下：
   如果只有一个任课教师 则在第一个位置上显示任课教师的名字 第二个位置为NULL
   如果有两个任课教师  则按姓名升序排列
   如果超过两个任课教师则 显示按姓名排序最靠前的教师姓名 第二个位置显示More



COURSE_NBR        INTEGER        课程号
STUDENT_NAME   VARCHAR2(10)       学生姓名
TEACHER_NAME   VARCHAR2(10)       教师姓名




create table REGISTER
(
  COURSE_NBR   INTEGER not null,
  STUDENT_NAME CHAR(10) not null,
  TEACHER_NAME CHAR(10) not null
)
;

prompt Loading REGISTER...
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (1, '1         ', 't10       ');
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (2, '2         ', 't21       ');
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (2, '2         ', 't22       ');
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (3, '3         ', 't31       ');
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (3, '3         ', 't32       ');
insert into REGISTER (COURSE_NBR, STUDENT_NAME, TEACHER_NAME)
values (3, '3         ', 't33       ');

COMMIT;

SELECT COURSE_NBR,
       MAX(CASE
             WHEN RN = 1 THEN
              TEACHER_NAME
           END),
       MAX(CASE
             WHEN RN = 2 AND CT = 1 THEN
              NULL
             WHEN RN = 2 AND CT = 2 THEN
              TEACHER_NAME
             WHEN RN = 2 AND CT > 2 THEN
              'more'
           END)
  FROM (SELECT COURSE_NBR,
               TEACHER_NAME,
               COUNT(*) OVER(PARTITION BY COURSE_NBR) CT,
               ROW_NUMBER() OVER(PARTITION BY COURSE_NBR ORDER BY TEACHER_NAME) RN
          FROM REGISTER T)
 GROUP BY COURSE_NBR

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24558043/viewspace-677562/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24558043/viewspace-677562/