下面每一行都是表table_A中字段colum_A的值,每一个值都是由逗号分隔、多个整数组成的数字串:1,102,
1,105,
2,205,
1,101,10102,1010202,
2,202,20203,
2,203,20303,
1,104,10402,
1,105,21,
2,206,50008164,50008822,
1,105,50008164,50001705,211509,
1,105,50008164,50001705,50008398,50008400,现在需要求出表中所有不同的整数并列出来,怎样用一个最简单的存储过程高效率的实现?
建议建立一个新表,专门存储table_A里出现过的数值的数据 那样统计起来不就方便多了
declare @nestr nvarchar(4000)
declare @colum_A nvarchar(500)
declare @Subcolum_A nvarchar(500)
set @nestr=''
declare cs CURSOR for select colum_A from table_A
open cs
FETCH NEXT FROM cs into @colum_A
while (@@fetch_status = 0)
begin
while (ltrim(rtrim(@colum_A))<>'' and ltrim(rtrim(@colum_A))<>',')
begin
set @Subcolum_A=SUBSTRING(@colum_A,0,CHARINDEX(',',@colum_A)+1)
set @colum_A=SUBSTRING(@colum_A,CHARINDEX(',',@colum_A)+1,len(@colum_A)-CHARINDEX(',',@colum_A)+1)
print @Subcolum_A
if (CHARINDEX(@Subcolum_A,@nestr)=0) set @nestr=@nestr+@Subcolum_A
end FETCH NEXT FROM cs into @colum_A
end
CLOSE cs
DEALLOCATE csprint @nestr
declare @colum_A nvarchar(500)
declare @Subcolum_A nvarchar(500)
set @nestr=''
declare cs CURSOR for select colum_A from table_A
open cs
FETCH NEXT FROM cs into @colum_A
while (@@fetch_status = 0)
begin
while (ltrim(rtrim(@colum_A))<>'' and ltrim(rtrim(@colum_A))<>',')
begin
set @Subcolum_A=SUBSTRING(@colum_A,0,CHARINDEX(',',@colum_A)+1)
set @colum_A=SUBSTRING(@colum_A,CHARINDEX(',',@colum_A)+1,len(@colum_A)-CHARINDEX(',',@colum_A)+1)
if (CHARINDEX(','+@Subcolum_A,@nestr)=0)
begin
if (@nestr='') set @nestr=@nestr+','+@Subcolum_A
else set @nestr=@nestr+@Subcolum_A
end
end FETCH NEXT FROM cs into @colum_A
end
CLOSE cs
DEALLOCATE csset @nestr=SUBSTRING(@nestr,2,len(@nestr)-1)
print @nestr
insert into tb
select '1,102,' union all
select '1,105,' union all
select '2,205,' union all
select '1,101,10102,1010202,' union all
select '2,202,20203,' union all
select '2,203,20303,' union all
select '1,104,10402,' union all
select '1,105,21,' union all
select '2,206,50008164,50008822,' union all
select '1,105,50008164,50001705,211509,' union all
select '1,105,50008164,50001705,50008398,50008400,'--临时表
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b SELECT
distinct col = SUBSTRING(A.col, B.ID, CHARINDEX(',', A.col + ',', B.ID) - B.ID)
FROM tb A, tmp B
WHERE SUBSTRING(',' + a.col, B.id, 1) = ','
ORDER BY col
GOdrop table tb , tmp
create table b (col1 int)
declare @str1 varchar(300), @str2 varchar(100)
set @str1=''
set @str2=''
declare cs CURSOR for select agentpath from CommodityBargain where agentpath <> ''
open cs
FETCH NEXT FROM cs into @str1
while (@@fetch_status = 0)
begin
while(charindex(',',@str1)>0)
begin
set @str2 = replace(left(@str1,charindex(',',@str1)),',','')
insert into b select @str2
set @str1 = substring(@str1,len(@str2)+2,len(@str1)-(len(@str2)+1))
end
FETCH NEXT FROM cs into @str1
end
CLOSE cs
DEALLOCATE cs
select distinct col1 from b order by col1 asc
(charindex(',',reverse([agentpath]),2)-2)<=0
then len([agentpath])-1 else charindex(',',reverse([agentpath]),2)-2 end)) as int)
from CommodityBargain where agentpath <> ''
order by agentpath asc
cursor emp_cursor is select id,replace(emp,',','') emp from z_test1;
i int;
totalnum int;
begin
for emp_record in emp_cursor loop
select length(replace(emp,',','')) into totalnum from z_test1 where id=emp_record.id;
for i in 1..totalnum loop
--dbms_output.put_line(substr(emp_record.emp,i,1));
insert into z_test2 values(substr(emp_record.emp,i,1));
commit;
end loop;
end loop;
end ;
----------------------------------------
select * from z_test2 order by result asc