Oracle 字段检索问题

[snoopy_wx]

我有一个table，字段包括： 编号、状态、数量
编号可以重复
现在我要分别获得状态为‘01’、‘02’、‘03’的数量和编号
然后分别显示编号相同的数量
如状态为‘01’的编号和数量为A.no、A.num
状态为‘02’的编号和数量为B.no、B.num
状态为‘03’的编号和数量为C.no、C.num
我要显示的是 当A.no=B.no AND A.no=C.no时的A.num,B.num,C.num
请各位大象帮忙想想办法，以最简单的方法来实现

[superrg]

select a.Num as aNum,b.Num as bNum,c.Num as cNum
from table a,table b,table c
where a.state = '01' and b.state = '02' and c.state = '03'
and a.no = b.no and a.no = c.no;

[KingSunSha]

假定每个no都有3种状态
SELECT a.no, a.num, b.num, c.num
FROM (SELECT no,num FROM mytable WHERE status = '01') A,
(SELECT no,num FROM mytable WHERE status = '02') B,
(SELECT no,num FROM mytable WHERE status = '03') C,
WHERE a.no = b.no
AND a.no = c.no;

[snoopy_wx]

very very thankyou!
我想给你加分，不知道怎么加