解决网友相关问题

ITEM COUNT
---- -------
A001  3;
A002  2;
A003  0;

上面是表的2个字段，怎样通过查询语句得到下面的结果

ITEM XH
---- --
A001  1;
A001  2;
A001  3;
A002  1;
A002  2;

 

解决办法:

--SQL:
with temp
as
(
select 'A001' a,3 b from dual
union all
select 'A002',2 from dual
union all
select 'A003',0 from dual
)
select a,rn from temp,
(select rownum rn from dual connect by rownum<4) a
where b>0 and rn<=b
order by a

--result:
A001 1
A001 2
A001 3
A002 1
A002 2