--你去试试速度,另外注意,不要把你自己原来的表删除了.临时表可以删除.create table tb(编号 varchar(10),颜色 varchar(10),形状 varchar(10)) insert into tb values('001' ,'红色' ,'圆形') insert into tb values('005' ,'红色' ,'圆形') insert into tb values('006' ,'红色' ,'圆形') insert into tb values('008' ,'红色' ,'圆形') insert into tb values('011' ,'黑色' ,'球形') insert into tb values('015' ,'黄色' ,'方形') insert into tb values('026' ,'红色' ,'圆形') insert into tb values('031' ,'红色' ,'圆形') insert into tb values('035' ,'黄色' ,'球形') insert into tb values('036' ,'黑色' ,'球形') insert into tb values('039' ,'黑色' ,'球形') insert into tb values('055' ,'黑色' ,'球形') go create procedure my_proc as begin declare @颜色1 as varchar(10) declare @形状1 as varchar(10) declare @颜色2 as varchar(10) declare @形状2 as varchar(10) set @颜色1 = '' set @形状1 = '' set @颜色2 = '' set @形状2 = '' declare @i as int declare @j as int declare @cnt as int set @i = 1 set @j = 1 set @cnt = (select count(*) from tmp1) while @i <= @cnt begin set @颜色2 = (select 颜色 from tmp1 where px = @i) set @形状2 = (select 形状 from tmp1 where px = @i) if @i = 1 begin set @颜色1 = @颜色2 set @形状1 = @形状2 set @j = 1 end else if @颜色2 <> @颜色1 or @形状2 <> @形状1 begin insert into tmp2 values(cast(@j as varchar) + @颜色1 + @形状1) set @颜色1 = @颜色2 set @形状1 = @形状2 set @j = 1 end else if @i <> @cnt set @j = @j + 1 else begin insert into tmp2 values(cast(@j + 1 as varchar) + @颜色1 + @形状1) end set @i = @i + 1 end end goselect * , px = identity(int,1,1) into tmp1 from tb create table tmp2(col varchar(10)) goexec my_procselect * from tmp2drop table tb , tmp1 , tmp2 drop procedure my_proc/* col ---------- 4红色圆形 1黑色球形 1黄色方形 2红色圆形 1黄色球形 3黑色球形(所影响的行数为 6 行)*/
--稍微修改了一下,应该比上个快些. --你去试试速度,另外注意,不要把你自己原来的表删除了.临时表可以删除. create table tb(编号 varchar(10),颜色 varchar(10),形状 varchar(10)) insert into tb values('001' ,'红色' ,'圆形') insert into tb values('005' ,'红色' ,'圆形') insert into tb values('006' ,'红色' ,'圆形') insert into tb values('008' ,'红色' ,'圆形') insert into tb values('011' ,'黑色' ,'球形') insert into tb values('015' ,'黄色' ,'方形') insert into tb values('026' ,'红色' ,'圆形') insert into tb values('031' ,'红色' ,'圆形') insert into tb values('035' ,'黄色' ,'球形') insert into tb values('036' ,'黑色' ,'球形') insert into tb values('039' ,'黑色' ,'球形') insert into tb values('055' ,'黑色' ,'球形') go create procedure my_proc as begin declare @颜色1 as varchar(10) declare @形状1 as varchar(10) declare @颜色2 as varchar(10) declare @形状2 as varchar(10) set @颜色1 = (select 颜色 from tmp1 where px = 1) set @形状1 = (select 形状 from tmp1 where px = 1) set @颜色2 = '' set @形状2 = '' declare @i as int declare @j as int declare @cnt as int set @i = 2 set @j = 1 set @cnt = (select count(*) from tmp1) while @i <= @cnt begin set @颜色2 = (select 颜色 from tmp1 where px = @i) set @形状2 = (select 形状 from tmp1 where px = @i) if @颜色2 <> @颜色1 or @形状2 <> @形状1 begin insert into tmp2 values(cast(@j as varchar) + @颜色1 + @形状1) set @颜色1 = @颜色2 set @形状1 = @形状2 set @j = 1 end else if @i <> @cnt set @j = @j + 1 else begin insert into tmp2 values(cast(@j + 1 as varchar) + @颜色1 + @形状1) end set @i = @i + 1 end end goselect * , px = identity(int,1,1) into tmp1 from tb create table tmp2(col varchar(10)) goexec my_procselect * from tmp2drop table tb , tmp1 , tmp2 drop procedure my_proc/* col ---------- 4红色圆形 1黑色球形 1黄色方形 2红色圆形 1黄色球形 3黑色球形(所影响的行数为 6 行)*/
if object_id('tb') is not null drop table tb go create table tb(编号 varchar(10),颜色 varchar(10),形状 varchar(10)) insert into tb values('001' ,'红色' ,'圆形') insert into tb values('005' ,'红色' ,'圆形') insert into tb values('006' ,'红色' ,'圆形') insert into tb values('008' ,'红色' ,'圆形') insert into tb values('011' ,'黑色' ,'球形') insert into tb values('015' ,'黄色' ,'方形') insert into tb values('026' ,'红色' ,'圆形') insert into tb values('031' ,'红色' ,'圆形') insert into tb values('035' ,'黄色' ,'球形') insert into tb values('036' ,'黑色' ,'球形') insert into tb values('039' ,'黑色' ,'球形') insert into tb values('055' ,'黑色' ,'球形') godeclare @颜色形状 varchar(20),@P颜色形状 varchar(20),@i int; declare @t table(col varchar(20)); declare cur cursor for select 颜色+形状 from tb order by 编号; open cur; fetch next from cur into @颜色形状; select @i=0,@P颜色形状=@颜色形状; while @@fetch_status = 0 begin if @颜色形状<>@P颜色形状 begin insert @t values(ltrim(@i)+@P颜色形状); select @i=0,@P颜色形状=@颜色形状; end select @i=@i+1,@P颜色形状=@颜色形状; fetch next from cur into @颜色形状; end insert @t values(ltrim(@i)+@P颜色形状); close cur; deallocate cur; select * from @t/*-->结果 col -------------------- 4红色圆形 1黑色球形 1黄色方形 2红色圆形 1黄色球形 3黑色球形(6 行受影响) */
if object_id('tb') is not null drop table tb go create table tb(编号 varchar(10),颜色 varchar(10),形状 varchar(10)) insert into tb values('001' ,'红色' ,'圆形') insert into tb values('005' ,'红色' ,'圆形') insert into tb values('006' ,'红色' ,'圆形') insert into tb values('008' ,'红色' ,'圆形') insert into tb values('011' ,'黑色' ,'球形') insert into tb values('015' ,'黄色' ,'方形') insert into tb values('026' ,'红色' ,'圆形') insert into tb values('031' ,'红色' ,'圆形') insert into tb values('035' ,'黄色' ,'球形') insert into tb values('036' ,'黑色' ,'球形') insert into tb values('039' ,'黑色' ,'球形') insert into tb values('055' ,'黑色' ,'球形') goselect fid=0,id=0,颜色+形状 as 颜色形状 into # from tb order by 编号 declare @颜色形状 varchar(20),@i int,@j int update # set id=@i,fid=@j, @i=case when @颜色形状=颜色形状 then isnull(@i,0)+1 else 1 end, @j=case when @颜色形状=颜色形状 then isnull(@j,0) else isnull(@j,0)+1 end, @颜色形状=颜色形状 select ltrim(max(id))+颜色形状 as 结果 from # group by fid,颜色形状 order by fid drop table #/* 结果 -------------------------------- 4红色圆形 1黑色球形 1黄色方形 2红色圆形 1黄色球形 3黑色球形(6 行受影响) */
insert into tb values('001' ,'红色' ,'圆形')
insert into tb values('005' ,'红色' ,'圆形')
insert into tb values('006' ,'红色' ,'圆形')
insert into tb values('008' ,'红色' ,'圆形')
insert into tb values('011' ,'黑色' ,'球形')
insert into tb values('015' ,'黄色' ,'方形')
insert into tb values('026' ,'红色' ,'圆形')
insert into tb values('031' ,'红色' ,'圆形')
insert into tb values('035' ,'黄色' ,'球形')
insert into tb values('036' ,'黑色' ,'球形')
insert into tb values('039' ,'黑色' ,'球形')
insert into tb values('055' ,'黑色' ,'球形')
go
create procedure my_proc
as
begin
declare @颜色1 as varchar(10)
declare @形状1 as varchar(10)
declare @颜色2 as varchar(10)
declare @形状2 as varchar(10)
set @颜色1 = ''
set @形状1 = ''
set @颜色2 = ''
set @形状2 = ''
declare @i as int
declare @j as int
declare @cnt as int
set @i = 1
set @j = 1
set @cnt = (select count(*) from tmp1)
while @i <= @cnt
begin
set @颜色2 = (select 颜色 from tmp1 where px = @i)
set @形状2 = (select 形状 from tmp1 where px = @i)
if @i = 1
begin
set @颜色1 = @颜色2
set @形状1 = @形状2
set @j = 1
end
else
if @颜色2 <> @颜色1 or @形状2 <> @形状1
begin
insert into tmp2 values(cast(@j as varchar) + @颜色1 + @形状1)
set @颜色1 = @颜色2
set @形状1 = @形状2
set @j = 1
end
else
if @i <> @cnt
set @j = @j + 1
else
begin
insert into tmp2 values(cast(@j + 1 as varchar) + @颜色1 + @形状1)
end
set @i = @i + 1
end
end
goselect * , px = identity(int,1,1) into tmp1 from tb
create table tmp2(col varchar(10))
goexec my_procselect * from tmp2drop table tb , tmp1 , tmp2
drop procedure my_proc/*
col
----------
4红色圆形
1黑色球形
1黄色方形
2红色圆形
1黄色球形
3黑色球形(所影响的行数为 6 行)*/
--你去试试速度,另外注意,不要把你自己原来的表删除了.临时表可以删除.
create table tb(编号 varchar(10),颜色 varchar(10),形状 varchar(10))
insert into tb values('001' ,'红色' ,'圆形')
insert into tb values('005' ,'红色' ,'圆形')
insert into tb values('006' ,'红色' ,'圆形')
insert into tb values('008' ,'红色' ,'圆形')
insert into tb values('011' ,'黑色' ,'球形')
insert into tb values('015' ,'黄色' ,'方形')
insert into tb values('026' ,'红色' ,'圆形')
insert into tb values('031' ,'红色' ,'圆形')
insert into tb values('035' ,'黄色' ,'球形')
insert into tb values('036' ,'黑色' ,'球形')
insert into tb values('039' ,'黑色' ,'球形')
insert into tb values('055' ,'黑色' ,'球形')
go
create procedure my_proc
as
begin
declare @颜色1 as varchar(10)
declare @形状1 as varchar(10)
declare @颜色2 as varchar(10)
declare @形状2 as varchar(10)
set @颜色1 = (select 颜色 from tmp1 where px = 1)
set @形状1 = (select 形状 from tmp1 where px = 1)
set @颜色2 = ''
set @形状2 = '' declare @i as int
declare @j as int
declare @cnt as int
set @i = 2
set @j = 1
set @cnt = (select count(*) from tmp1)
while @i <= @cnt
begin
set @颜色2 = (select 颜色 from tmp1 where px = @i)
set @形状2 = (select 形状 from tmp1 where px = @i)
if @颜色2 <> @颜色1 or @形状2 <> @形状1
begin
insert into tmp2 values(cast(@j as varchar) + @颜色1 + @形状1)
set @颜色1 = @颜色2
set @形状1 = @形状2
set @j = 1
end
else
if @i <> @cnt
set @j = @j + 1
else
begin
insert into tmp2 values(cast(@j + 1 as varchar) + @颜色1 + @形状1)
end
set @i = @i + 1
end
end
goselect * , px = identity(int,1,1) into tmp1 from tb
create table tmp2(col varchar(10))
goexec my_procselect * from tmp2drop table tb , tmp1 , tmp2
drop procedure my_proc/*
col
----------
4红色圆形
1黑色球形
1黄色方形
2红色圆形
1黄色球形
3黑色球形(所影响的行数为 6 行)*/
go
create table tb(编号 varchar(10),颜色 varchar(10),形状 varchar(10))
insert into tb values('001' ,'红色' ,'圆形')
insert into tb values('005' ,'红色' ,'圆形')
insert into tb values('006' ,'红色' ,'圆形')
insert into tb values('008' ,'红色' ,'圆形')
insert into tb values('011' ,'黑色' ,'球形')
insert into tb values('015' ,'黄色' ,'方形')
insert into tb values('026' ,'红色' ,'圆形')
insert into tb values('031' ,'红色' ,'圆形')
insert into tb values('035' ,'黄色' ,'球形')
insert into tb values('036' ,'黑色' ,'球形')
insert into tb values('039' ,'黑色' ,'球形')
insert into tb values('055' ,'黑色' ,'球形')
godeclare @颜色形状 varchar(20),@P颜色形状 varchar(20),@i int;
declare @t table(col varchar(20));
declare cur cursor for select 颜色+形状 from tb order by 编号;
open cur;
fetch next from cur into @颜色形状;
select @i=0,@P颜色形状=@颜色形状;
while @@fetch_status = 0
begin
if @颜色形状<>@P颜色形状
begin
insert @t values(ltrim(@i)+@P颜色形状);
select @i=0,@P颜色形状=@颜色形状;
end
select @i=@i+1,@P颜色形状=@颜色形状;
fetch next from cur into @颜色形状;
end
insert @t values(ltrim(@i)+@P颜色形状);
close cur;
deallocate cur;
select * from @t/*-->结果
col
--------------------
4红色圆形
1黑色球形
1黄色方形
2红色圆形
1黄色球形
3黑色球形(6 行受影响)
*/
go
create table tb(编号 varchar(10),颜色 varchar(10),形状 varchar(10))
insert into tb values('001' ,'红色' ,'圆形')
insert into tb values('005' ,'红色' ,'圆形')
insert into tb values('006' ,'红色' ,'圆形')
insert into tb values('008' ,'红色' ,'圆形')
insert into tb values('011' ,'黑色' ,'球形')
insert into tb values('015' ,'黄色' ,'方形')
insert into tb values('026' ,'红色' ,'圆形')
insert into tb values('031' ,'红色' ,'圆形')
insert into tb values('035' ,'黄色' ,'球形')
insert into tb values('036' ,'黑色' ,'球形')
insert into tb values('039' ,'黑色' ,'球形')
insert into tb values('055' ,'黑色' ,'球形')
goselect fid=0,id=0,颜色+形状 as 颜色形状 into # from tb order by 编号
declare @颜色形状 varchar(20),@i int,@j int
update # set id=@i,fid=@j,
@i=case when @颜色形状=颜色形状 then isnull(@i,0)+1 else 1 end,
@j=case when @颜色形状=颜色形状 then isnull(@j,0) else isnull(@j,0)+1 end,
@颜色形状=颜色形状
select ltrim(max(id))+颜色形状 as 结果 from # group by fid,颜色形状 order by fid
drop table #/*
结果
--------------------------------
4红色圆形
1黑色球形
1黄色方形
2红色圆形
1黄色球形
3黑色球形(6 行受影响)
*/