SQL数据如何循环？
表结构如下：
os_no pn_po
------------------------------------------------------------
SOBZ111121LOG01PO-138490
SOBZ130819ZAL01PO-1006818/PO-1006820/PO-1006823/PO-1006825
SOBZ130517FRE01PO-1004245/PO-1004248/PO-1004426/PO-1004661
------------------------------------------------------------
查询结果格式如下:
so_no po
------------------------------------------------------------
SOBZ130517FRE01 PO-1004245
SOBZ130517FRE01 PO-1004248
SOBZ130517FRE01 PO-1004426
SOBZ130517FRE01 PO-1004661
SOBZ111121LOG01 PO-138490
SOBZ130819ZAL01 PO-1006818
............... ..........
--------------------------------------------------------------
declare @num int
declare @i int
declare @k int
declare @f int
declare @temp table(po varchar(20),so_no varchar(20))
set @i=0
set @f=0
declare @s varchar(400)
declare @po varchar(20)
declare @so_no varchar(20)
select @num=len(pn_po)-len(replace(pn_po,'/','')),@s=pn_po,@k=charindex('/',pn_po),@so_no=os_no from mf_pos_z
where os_no='SOBZ130819ZAL01' or os_no='SOBZ130517FRE01' or os_no='SOBZ111121LOG01'
--这儿如何加一个大循环,因为下面小循环中有给变量赋值的语句,不能用游标啊
while(@i<=@num)
begin
if(@k>1)
begin
set @po=left(@s,@k-1)
set @s=substring(@s,@k+1,len(@s)-@k+1)
insert into @temp(po,so_no)
values(@po,@so_no)
end
else
begin
insert into @temp(po,so_no)
values(@s,@so_no)
end
set @i=@i+1
end
select * from @temp
