SQL½â»ó

Create Table Projects
( workorder_id char(5) not null,
  step_nbr int 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));

workorder_id step_nbr  step_status
AA100   0 C
AA100   1 W
AA100   2 W
AA200   0 W
AA200   1 W
AA300   0 C
AA300   1 C

ÒªµÃµ½step_nbr ÊÇ 0 ²¢ÇÒ step_status='C'µÄ workorder_id µ«ÊÇÕâ¸ö˳ÐòµÄËùÓÐÆäËüµÄ·ÖÖ§µÄstep_status='W'

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);

¶ÔÓÚ else NULL ÔõôÀí½â°¡  ÎÒÔËÐÐ Select Count(NULL)ÊDz»ÄÜͨ¹ýµÄ°¡ .!

---

ÊÇcase when ºóÃæµÄÅжϰ¡
Èç¹û step_nbr=0  and step_status='c' Ôò¼ÇÊýÒ»´Î ·ñÔònull
²»¹ý²»Àí½â²»else 0

---

±ÈÈçÕâÌõ:
AA200  0 W

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);
ÕâÁ½¸ö¶¼²»³ÉÁ¢°¡  ÄDz»ÊÇ Count(NULL)+Count(NULL)
{{----}