create table tba_spbm(bm varchar(6),sl int)
go
declare @sl varchar(8000),@bm varchar(8000)
set @sl='500/600/700/800/900/1000/'
set @bm='p00001/p00002/p00003/p00003/p00004/p00005/'
while (charindex('/',@sl)>0)
begin
insert into tba_spbm select left(@bm,charindex('/',@bm)-1),left(@sl,charindex('/',@sl)-1)
set @sl=substring(@sl,charindex('/',@sl)+1,len(@sl)-charindex('/',@sl))
set @bm=substring(@bm,charindex('/',@bm)+1,len(@bm)-charindex('/',@bm))
end
go
select * from tba_spbm;
go
drop table tba_spbm;
go--结果
bm sl
------ -----------
p00001 500
p00002 600
p00003 700
p00003 800
p00004 900
p00005 1000(所影响的行数为 6 行)
刚解决的还有点小问题,插入时如果bm重复如何提醒哪个bm重复
go
declare @sl varchar(8000),@bm varchar(8000)
set @sl='500/600/700/800/900/1000/'
set @bm='p00001/p00002/p00003/p00003/p00004/p00005/'
while (charindex('/',@sl)>0)
begin
insert into tba_spbm select left(@bm,charindex('/',@bm)-1),left(@sl,charindex('/',@sl)-1)
set @sl=substring(@sl,charindex('/',@sl)+1,len(@sl)-charindex('/',@sl))
set @bm=substring(@bm,charindex('/',@bm)+1,len(@bm)-charindex('/',@bm))
end
go
select * from tba_spbm;
go
drop table tba_spbm;
go--结果
bm sl
------ -----------
p00001 500
p00002 600
p00003 700
p00003 800
p00004 900
p00005 1000(所影响的行数为 6 行)
刚解决的还有点小问题,插入时如果bm重复如何提醒哪个bm重复
create table tba_spbm(bm varchar(6),sl int)
go
declare @sl varchar(8000),@bm varchar(8000) set @sl='500/600/700/800/900/1000/'
set @bm='p00001/p00002/p00003/p00003/p00004/p00005/'
while (charindex('/',@sl)>0)
begin
if exists(select * from tba_spbm where bm = @bm)--判断这个表里面有没有存在,有的话就提醒或是其它处理一下
begin
....
end
insert into tba_spbm select left(@bm,charindex('/',@bm)-1),left(@sl,charindex('/',@sl)-1)
set @sl=substring(@sl,charindex('/',@sl)+1,len(@sl)-charindex('/',@sl))
set @bm=substring(@bm,charindex('/',@bm)+1,len(@bm)-charindex('/',@bm))
end
go
select * from tba_spbm;
go
drop table tba_spbm;
go