# 【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