【SQL解惑】谜题14：电话

解惑一：

1、创建表和插入数据

create table Personnel

( emp_id integer primary key ,

first_name char ( 20 ) not null,

last_name char ( 20 ) not null)

create table Phones

( emp_id integer not null,

phone_type char ( 3 ) not null

            check ( phone_type in ( 'hom' , 'fax' )),

phone_nbr char ( 12 ) not null,

primary key ( emp_id , phone_type ),

foreign key ( emp_id ) references Personnel ( emp_id ))

insert into Personnel ( emp_id , first_name , last_name )

values ( 1 , 'La' , 'Done' ),

( 2 , 'Guo' , 'Yin' ),

( 3 , 'Tim' , 'Gour' )

insert into Phones ( emp_id , phone_type , phone_nbr )

values ( 1 , 'hom' , '8845123' ),

( 2 , 'hom' , '8845123' ),

( 3 , 'hom' , '8845123' )

go

2、创建视图

create view FaxPhones ( last_name , first_name , emp_id , fax_phone )

as select e1 . last_name , e1 . first_name , e1 . emp_id , f1 . phone_nbr

       from ( Personnel as e1

               left outer join

               Phones as f1

               on e1 . emp_id = f1 . emp_id

               and f1 . phone_type = 'fax' )

go     

create view HomePhones ( last_name , first_name , emp_id , home_phone )

as select e1 . last_name , e1 . first_name , e1 . emp_id , f1 . phone_nbr

       from ( Personnel as e1

               left outer join

               Phones as f1

               on e1 . emp_id = f1 . emp_id

               and f1 . phone_type = 'hom' )    

go

select h1 . last_name , h1 . first_name , home_phone , fax_phone

from HomePhones as h1 , FaxPhones as f1

where h1 . emp_id = f1 . emp_id

go

解惑二：

1、from子句中，从Personel表中，左外连接Phones表，匹配emp_id和phone_type标识

select e1 . last_name , e1 . first_name ,

         h1 . phone_nbr as Home ,

         f1 . phone_nbr as Fax

from ( Personnel as e1

        left outer join

        Phones as h1

        on e1 . emp_id = h1 . emp_id

             and h1 . phone_type = 'hom' )

        left outer join

        Phones as f1

        on e1 . emp_id = f1 . emp_id

             and f1 . phone_type = 'fax'

解惑三：

select e1 . emp_id , e1 . first_name , e1 . last_name ,

         max ( case when p1 . phone_type = 'hom'

                         then p1 . phone_nbr

                         else null end ) as home_phone ,

         max ( case when p1 . phone_type = 'fax'

                         then p1 . phone_nbr

                         else null end ) as fax_phone

from Personnel as e1

left outer join

Phones as p1

on p1 . emp_id = e1 . emp_id

group by e1 . emp_id , e1 . first_name , e1 . last_name

解惑四：

select p1 . last_name , p1 . first_name ,

         ( select t1 . phone_nbr

              from Phones as t1

             where t1 . emp_id = p1 . emp_id

               and t1 . phone_type = 'hom' ) as home_phone ,

         ( select t2 . phone_nbr

              from Phones as t2

             where t2 . emp_id = p1 . emp_id

               and t2 . phone_type = 'fax' ) as fax_phone

from Personnel as p1