【SQL解惑】谜题11：工作顺序

解惑一：

1、创建表并插入数据

create table Projects

(

workorder_id char ( 5 ) not null,

step_nbr integer not null check ( step_nbr between 0 and 1000 ),

step_status char ( 1 ) not null

       check ( step_status in ( 'C' , 'W' )),

       primary key ( workorder_id , step_nbr )

)

insert into Projects ( workorder_id , step_nbr , step_status )

values ( 'AA100' , 0 , 'C' ),

( 'AA100' , 1 , 'W' ),

( 'AA100' , 2 , 'W' ),

( 'AA200' , 0 , 'W' ),

( 'AA200' , 1 , 'W' ),

( 'AA300' , 0 , 'C' ),

( 'AA300' , 1 , 'C' )

2、实际上要找到的工序即是第0道工序为为完成Complete，其他非0工序为Waiting

select workorder_id

  from Projects as p1

  where step_nbr = 0

   and step_status = 'C'

   and 'W' = ALL( select step_status

                           from Projects as p2

                          where step_nbr <> 0

                            and p1 . workorder_id = p2 . workorder_id )

解惑二：

1、当第0道工序为Complete和非0工序为Waiting时则等于1，当工序的累加=Count函数时则证明满足

select workorder_id

from Projects

group by workorder_id

having SUM ( case          

              when step_nbr <> 0 and step_status = 'W' then 1

              when step_nbr = 0 and step_status = 'C' then 1

              else 0 end ) = COUNT ( step_nbr )

解惑三：

1、方法一

select workorder_id

       from Projects

       group by workorder_id

       having COUNT (*) = COUNT ( case when step_nbr = 0 and step_status = 'C'

                                           then 1

                                           else null end )

                               + COUNT ( case when step_nbr <> 0 and step_status = 'W'

                                           then 1

                                           else null end )

2、方法二：利用了not null和check()约束

select workorder_id

from Projects

where step_status = 'C'

group by workorder_id

having SUM ( step_nbr ) = 0