一个过程接收一个参数.一个参数格式分别为
参数格式:1,2,3,4,5,6,7,8....
我如何用最高的效率把1,2,3,4,5作为单条记录存放在表中,并保证表中不存在相同的记录?需要说明的是1,2,3,4....是一个varchar(8000)的变量~请各位指定一二~
参数格式:1,2,3,4,5,6,7,8....
我如何用最高的效率把1,2,3,4,5作为单条记录存放在表中,并保证表中不存在相同的记录?需要说明的是1,2,3,4....是一个varchar(8000)的变量~请各位指定一二~
set @s='1,2,3,4,2,5,6,7,8'
select @s=replace(@s,',',' union select ')
exec('select col='+@s)
--@s很長時,可以考慮function,效率可能不咋地
Create FUNCTION SplitList
(@separator char(1), @List varchar(8000))
RETURNS @ReturnTable TABLE(ListItem varchar(1000) COLLATE Database_Default)
AS
BEGIN
DECLARE @Index int
DECLARE @NewText varchar(8000)
IF @List = null
RETURN
SET @Index = CHARINDEX(@separator, @List)
WHILE NOT(@Index = 0)
BEGIN
SET @NewText = RTRIM(LTRIM(LEFT(@List, @Index - 1)))
SET @List = RIGHT(@List, LEN(@List) - @Index)
INSERT INTO @ReturnTable(ListItem) VALUES(@NewText)
SET @Index = CHARINDEX(@separator, @List)
END
INSERT INTO @ReturnTable(ListItem) VALUES(RTRIM(LTRIM(@List)))
RETURN
END
declare @s varchar(8000)
set @s='1,2,3,4,5,6,7,8,9,0,12,1,4,14,87,52,1,2,32,,13,43,2,3212,543,12'
select distinct * from dbo.SplitList(',',@s)
declare @s varchar(5000)
set @s='1,2,3,4,2,5,6,7,8'
select @s=replace(@s,',',' union select ')
exec('select col='+@s)
这样就可以拉!
(
@b varchar(8000),
@ref varchar(256)
)
asif right(@b,1)=','
select @b=left(@b,len(@b)-1)
declare @sql varchar(8000)
select @sql= 'select '''+replace(@b,',',''' union all select ''')+''''
create table #(id int identity(1,1),val varchar(20))
insert into #(val)
exec(@sql)
delete # where val in(select QQ from temp_qq)--select id,val from # --此处最后更换为你的插入表语句。insert into temp_qq(QQ,ref) select val ,@ref from #
drop table # --删除临时表
(*蓝星之梦*) 的思路不错,改进了下....