日期:2014-05-17  浏览次数:20396 次

SQL数据如何循环?
表结构如下:
os_no         pn_po
------------------------------------------------------------
SOBZ111121LOG01 PO-138490
SOBZ130819ZAL01 PO-1006818/PO-1006820/PO-1006823/PO-1006825
SOBZ130517FRE01 PO-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

------解决方案--------------------
http://blog.csdn.net/wufeng4552/article/details/4534365
------解决方案--------------------
create table #table1(
os_no varchar(100) null,
pn_po varchar(500) null
)