【SQL解惑】谜题8：调度打印机

解惑一：

create table PrinterControl

(

       user_id_start char ( 10 ) not null,

       user_id_finish char ( 10 ) not null,

       printer_name char ( 4 ) not null,

       printer_description char ( 40 ) not null,

        primary key ( user_id_start , user_id_finish )

)

insert into PrinterControl ( user_id_start , user_id_finish , printer_name , printer_description )

values

( 'chacha' , 'chacha' , 'LPT1' , 'First floor''s printer' ),

( 'lee' , 'lee' , 'LPT2' , 'Seceond floor''s printer' ),

( 'thomas' , 'thomas' , 'LPT3' , 'Common printer #1' ),

( 'aaaaaaaa' , 'mzzzzzzz' , 'LPT4' , 'Common printer #1' ),

( 'naaaaaaa' , 'zzzzzzzz' , 'LPT5' , 'Common printer #2' )

begin

declare @my_user_id char ( 10 )

select MIN ( printer_name )

  from PrinterControl

  where @my_user_id between user_id_start and user_id_start

  end

解惑二：

create table PrinterControl

(

       user_id_1 char ( 10 ),

       printer_name char ( 4 ) not null primary key ,

       printer_description char ( 40 ) not null

)

insert into PrinterControl ( user_id_1 , printer_name , printer_description )

values

( 'chacha' , 'LPT1' , 'First floor''s printer' ),

( 'lee' , 'LPT2' , 'Seceond floor''s printer' ),

( 'thomas' , 'LPT3' , 'Common printer #1' ),

(null, 'LPT4' , 'Common printer #1' ),

(null, 'LPT5' , 'Common printer #2' )

使用 COALESCE 函数来进行判断，如果不为空则执行语句，获取user_id中不为空的最小型号的打印机。

begin

declare @my_user_id char ( 10 )

select COALESCE ( MIN ( printer_name ),

      ( select MIN ( printer_name )

         from PrinterControl as p2

        where user_id_1 is null))

from PrinterControl as p1

where user_id_1 = @my_user_id

end

解惑三：

begin

declare @my_user_id char ( 30 )

select coalesce ( min ( printer_name ),

               ( select distinct case

                                          when @my_user_id < 'n'

                                          then 'LPT4'

                                          else 'LPT5' end ))

from PrinterControl

where user_id_1 = @my_user_id

end

由于case语句固定了没有在使用的打印机，一旦使用则会无法查询到。

解惑四：

create table PrinterControl

(

user_id_1 char ( 10 ),

printer_name char ( 4 ) not null primary key ,

assignable_flag char ( 1 ) default 'Y' not null

                            check ( assignable_flag in ( 'Y' , 'N' )),

printer_decription char ( 40 ) not null

)

insert into PrinterControl ( user_id_1 , printer_name , printer_decription , assignable_flag )

values

( 'chacha' , 'LPT1' , 'First floor''s printer' , 'N' ),

( 'lee' , 'LPT2' , 'Seceond floor''s printer' , 'N' ),

( 'thomas' , 'LPT3' , 'Common printer #1' , 'N' ),

(null, 'LPT4' , 'Common printer #1' , 'Y' ),

(null, 'LPT5' , 'Common printer #2' , 'Y' )

更新标志为Y设置use_id 为最新id

begin

declare @my_user_id char ( 10 )

update PrinterControl

set user_id_1 = @my_user_id

where printer_name

        = ( select min ( printer_name )

               from PrinterControl

               where assignable_flag = 'Y'

               and user_id_1 is null)

end

清空所有标志为Y的打印机

update PrinterControl

set user_id_1 = null

where assignable_flag = 'Y'