【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