【SQL解惑】谜题13：教师

解惑一：

1、创建数据

create table Register

(

course_nbr integer not null,

student_name char ( 10 ) not null,

teacher_name char ( 10 ) not null

)

insert into Register ( course_nbr , student_name , teacher_name )

values ( 1 , 'Japser' , 'Yet' ),

( 1 , 'Japser' , 'Jucii' ),

( 1 , 'Japser' , 'Mole' ),

( 2 , 'Fue' , 'Orea' ),

( 2 , 'Fue' , 'Gucii' ),

( 2 , 'Fue' , 'Docde' )

select r1 . course_nbr , r1 . student_name ,

          min ( r1 . teacher_name ),null

from Register as r1

group by r1 . course_nbr , r1 . student_name

having count (*) = 1

union

select r1 . course_nbr , r1 . student_name ,

          min ( r1 . teacher_name ),

          max ( r1 . teacher_name )

from Register as r1

group by r1 . course_nbr , r1 . student_name

having COUNT (*) = 2

union

select r1 . course_nbr , r1 . student_name ,

          min ( r1 . teacher_name ), '--More--'

from Register as r1

group by r1 . course_nbr , r1 . student_name

having COUNT (*) > 2

解惑二：

使用case语句才实现判断，利用了表的每个值都为not null的方法。

select course_nbr , student_name , MIN ( teacher_name ),

         case COUNT (*) when 1 then null

                               when 2 then MAX ( teacher_name )

                               else '--More--' end

from Register

group by course_nbr , student_name

解惑三：

select case when teacher_name = ( select MIN ( teacher_name )

                                    from Register as r1

                                   where r1 . course_nbr = r0 . course_nbr

                                     and r1 . student_name = r0 . student_name )

                   then course_nbr

                   else '      ' end as course_nbr_hdr ,

         case when teacher_name = ( select MIN ( teacher_name )

                                      from Register as r1

                                     where r1 . course_nbr = r0 . course_nbr

                                       and r1 . student_name = r0 . student_name )

                   then student_name

                   else '      ' end as student_name_hdr ,

         teacher_name

from Register as r0

order by teacher_name