表a
a1 a2 a3 a4 a5 sort
1 2 3 4 5 1*2*3*4*5
5 2 3 4 1 1*2*3*4*5
5 3 4 2 1 1*2*3*4*5
1 5 6 7 8 1*5*6*7*8
1 5 6 8 7 1*5*6*7*8
8 7 6 5 1 1*5*6*7*8就是这样的表，原来sort字段是空的
我现在想update sort成上表所示
a1,a2,a3,a4,a5 为1-20的任意数字，并在同一条记录中不能重复。
怎样更新才能快呢？
我现在是用CLR的函数做的，好慢
180000条记录，要4s
如何才能加快速度呢？
sql 语句如下：
UPDATE a SET sort = dbo.fn_Sort(a1,a2,a3,a4,a5,'*')
a1 a2 a3 a4 a5 sort
1 2 3 4 5 1*2*3*4*5
5 2 3 4 1 1*2*3*4*5
5 3 4 2 1 1*2*3*4*5
1 5 6 7 8 1*5*6*7*8
1 5 6 8 7 1*5*6*7*8
8 7 6 5 1 1*5*6*7*8就是这样的表，原来sort字段是空的
我现在想update sort成上表所示
a1,a2,a3,a4,a5 为1-20的任意数字，并在同一条记录中不能重复。
怎样更新才能快呢？
我现在是用CLR的函数做的，好慢
180000条记录，要4s
如何才能加快速度呢？
sql 语句如下：
UPDATE a SET sort = dbo.fn_Sort(a1,a2,a3,a4,a5,'*')
drop table tb
gocreate table tb
(
a1 int,
a2 int,
a3 int,
a4 int,
a5 int,
sort varchar(100)
)insert into tb(a1,a2,a3,a4,a5,sort) values(1, 2, 3, 4, 5,null)
insert into tb(a1,a2,a3,a4,a5,sort) values(5, 2, 3, 4, 1,null)
insert into tb(a1,a2,a3,a4,a5,sort) values(5, 3, 4, 2, 1,null)
insert into tb(a1,a2,a3,a4,a5,sort) values(1, 5, 6, 7, 8,null)
insert into tb(a1,a2,a3,a4,a5,sort) values(1, 5, 6, 8, 7,null)
insert into tb(a1,a2,a3,a4,a5,sort) values(8, 7, 6, 5, 1,null)
goselect id=identity(int,1,1) , * into test from tbselect * into test2 from (
select id,a1 as a from test
union all
select id,a2 as a from test
union all
select id,a3 as a from test
union all
select id,a4 as a from test
union all
select id,a5 as a from test) t
order by id, a
go
--创建一个合并的函数
if object_id('pubs..f_hb') is not null
drop function f_hb
go
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '*' + cast(a as varchar) from test2 where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果：
select a.a1,a.a2,a.a3,a.a4,a.a5 , b.sort from test a,(
select distinct id ,dbo.f_hb(id) as sort from test2) b
where a.id = b.iddrop table tb
drop table test
drop table test2a1 a2 a3 a4 a5 sort
----------- ----------- ----------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 2 3 4 5 1*2*3*4*5
5 2 3 4 1 1*2*3*4*5
5 3 4 2 1 1*2*3*4*5
1 5 6 7 8 1*5*6*7*8
1 5 6 8 7 1*5*6*7*8
8 7 6 5 1 1*5*6*7*8（所影响的行数为 6 行）
sql server2005，不一定用函数做
只要速度快就行。
还有一个id的主键
----------- ----------- ----------- ----------- ----------- ----------
1 2 3 4 5 1*2*3*4*5
5 2 3 4 1 1*2*3*4*5
5 3 4 2 1 1*2*3*4*5
1 5 6 7 8 1*5*6*7*8
1 5 6 8 7 1*5*6*7*8
8 7 6 5 1 1*5*6*7*8（所影响的行数为 6 行）
这样肯定不行的。
老龟你的这个table test2 ，应该速度很慢的.
你想想180000条记录，180000*5,就120万条了。
如果再用函数查询.
insert ta
select 1, 2, 3, 4, 5 union all
select 5, 2, 3, 4, 1 union all
select 5, 3, 4, 2, 1 union all
select 1, 5, 6, 7, 8 union all
select 1, 5, 6, 8, 7 union all
select 8, 7, 6, 5, 1 alter table ta add 新列 varchar(20) null--新增列
declare @i int
set @i=0
update ta
set 新列=@i,@i=@i+1
--生成递增值--以下生成新表select * into tb
from (select 新列,a1 from ta
union all
select 新列,a2 from ta
union all
select 新列,a3 from ta
union all
select 新列,a4 from ta
union
select 新列,a5 from ta)a
order by 新列,a1(select *,记录=(select count(*) from tb where 新列=b.新列 and a1!>b.a1) into test_ta from tb b)--新表组合
--创建函数
create function test_fun(@新列 int)
returns varchar(1000)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+cast(a1 as varchar)+'*' from test_ta where 新列=@新列
select @sql=left(@sql,len(@sql)-1)
return @sql
end
select 新列,合成=dbo.test_fun(新列) from test_ta group by 新列--查看效果
--以下为更新
update ta
set 新列=dbo.test_fun(test_ta.新列)
from test_ta where test_ta.新列=ta.新列select * from ta--查看结果
a1 a2 a3 a4 a5 新列
----------- ----------- ----------- ----------- ----------- --------------------
1 2 3 4 5 1*2*3*4*5
5 2 3 4 1 1*2*3*4*5
5 3 4 2 1 1*2*3*4*5
1 5 6 7 8 1*5*6*7*8
1 5 6 8 7 1*5*6*7*8
8 7 6 5 1 1*5*6*7*8（所影响的行数为 6 行）--drop table test_ta,tb 删除新表的
--drop function test_fun 删更新函数--drop table ta 删测试原表
id int IDENTITY(1,1),
a1 int,
a2 int,
a3 int,
a4 int,
a5 int,
sort varchar(30)
)
insert @b(a1,a2,a3,a4,a5) select
1, 2, 3, 4, 5
union all select
5, 2, 3, 4, 1
union all select
5, 3, 4, 2, 1
union all select
1, 5, 6, 7, 8
union all select
1, 5, 6, 8, 7
union all select
8, 7, 6, 5, 1declare @t table (
id int,
a int
)insert @t
select id,a1 from @b
union all
select id,a2 from @b
union all
select id,a3 from @b
union all
select id,a4 from @b
union all
select id,a5 from @bwhile exists (select 1 from @t)
begin
update b
set sort=isnull(sort+'*','')+cast(t.a as varchar)
from @b b,@t t
where b.id=t.id
and not exists (
select 1 from @t where id=t.id and a<t.a
)delete t
from @t t
where not exists (
select 1 from @t where id=t.id and a<t.a
)end
select * from @b
你的算法要4分多钟
18万条记录