cidst和 cnamestr的对应关系是什么
解决方案 »
- SQL安装问题
- 请教,用RecordSet运行一段SQL脚本,怎么返回值呢?
- exists与 not exists 执行效率一样吗?会不会 exists会快些
- 【百分紧急求救】关于事物的几个疑惑点,希望各位老师帮助.
- 如何通过SQL的insert语句项数据表中添加中文记录
- 利用timestamp做增量数据导出的问题
- 在win2003的服务器上装sql2000,到启动画面,总自动退出
- 企业管理器不能用,提示如下错误,怎么办,那位大哥救救急!!!!!!!
- 存储过程如何操作另一个数据库中的表
- 高分!冷门问题!关于一个dbmagic的数据库
- SQL SERVER 2000安装问题
- 安装sqlserver失败,请教
1对应name1
2对应name2
3对应name3
id cidstr(字符串型) cnamestr(字符串型)
1 1,2,3,3,2 name1,name2,name3,name2,name3那么该如何得出你想要的结果?
create table #cname (id int identity(1,1), cname varchar(50))
declare @cidstr varchar(100), cnamestr varchar(500)
declare cursor1 cursor select cidstr,cnamestr from table1
open cursor1
fetch next from cursor1 into @cidstr, @cnamestr
while @@fetch_status = 0
begin
……………………
fetch next from cursor1 into @cidstr, @cnamestr
endclose cursor1
decllocate cursor1
--建立测试环境
Create table table1
(id Int,
cidstr Varchar(10),
cnamestr Varchar(20)
)
GO
--插入数据
Insert table1 Values (1,'1,2,3','name1,name2,name3')
GO
--建立函数
Create FunCtion SplitString(@cidstr Varchar(10),@cnamestr Varchar(20))
Returns @table Table(id Int,cidstr Varchar(10),cnamestr Varchar(10))
As
Begin
Declare @i1 Int
Declare @i2 Int
Declare @j Int
Set @i1=1
Set @i2=1
Set @j=1
While CharIndex(',',@cidstr)>0
Begin
Insert @table Values(@j,Left(@cidstr,CharIndex(',',@cidstr)-1),Left(@cnamestr,CharIndex(',',@cnamestr)-1))
Set @j=@j+1
Set @cidstr=Stuff(@cidstr,1,CharIndex(',',@cidstr),'')
Set @cnamestr=Stuff(@cnamestr,1,CharIndex(',',@cnamestr),'')
End
Insert @table Values(@j,@cidstr,@cnamestr)
Return
End
GO
--测试
Declare @cidstr Varchar(10)
Declare @cnamestr Varchar(20)
Select @cidstr=cidstr,@cnamestr=cnamestr from table1
Select * from dbo.SplitString(@cidstr,@cnamestr)
--删除测试环境
Drop table table1
Drop Function SplitString
--结果
/*
id cidstr cnamestr
1 1 name1
2 2 name2
3 3 name3
*/
create table table1(id int,cidstr varchar(100),cnamestr varchar(8000))
insert table1 select 1,'1,2,3','name1,name2,name3'
union all select 2,'77,78','name77,name88'
go--分拆查询
select top 8000 id=identity(int) into # from syscolumns a,syscolumns b
select a.id,
cidstr=substring(a.cidstr,b.id,charindex(',',a.cidstr+',',b.id)-b.id),
cnamestr=substring(a.cnamestr,b1.id,charindex(',',a.cnamestr+',',b1.id)-b1.id)
from table1 a,# b,# b1
where b.id<=len(a.cidstr) and substring(','+a.cidstr,b.id,1)=','
and b1.id<=len(a.cnamestr) and substring(','+a.cnamestr,b1.id,1)=','
and len(substring(a.cidstr,1,b.id))-len(replace(substring(a.cidstr,1,b.id),',',''))
=len(substring(a.cnamestr,1,b1.id))-len(replace(substring(a.cnamestr,1,b1.id),',',''))
drop table #
go--删除测试
drop table table1/*--结果
id cidstr cnamestr
----------- ------------------- ---------
1 1 name1
1 2 name2
1 3 name3
2 78 name88
2 77 name77(所影响的行数为 5 行)
--*/
insert @A VALUES(1 , '用户组1' , '0' , 1)
insert @A VALUES(2 , '用户组2' , '1' , 1)
insert @A VALUES(3 , '用户3 ' , '1,0' , 0)
insert @A VALUES(4 , '用户4' , '0,1,2' , 0)
insert @A VALUES(5 , '用户5' , '2,1,0' , 0)
insert @A VALUES(6 , '用户6' , '1,2' , 0)
insert @A VALUES(7 , '用户7' , '0' , 0)select m.userid,m.name,d.userid as parentid,m.type FROM @A m,
(select distinct userid from @A union select 0) d where charindex(','+cast(d.userid as varchar(10))+',',','+m.parentid+',')>0
order by d.userid,m.userid
--表 a(id1,id2)
id1 id2
2 1,2,3
2 3,5
3 2,3,5
3 5
--轉化成為表b
id1 id2
2 1
2 2
2 3
2 3
2 5
3 2
3 3
3 5
3 5
select * into b from a
delete b ---復制表結構到b
select * into #a from a
select * into #b from a
delete #b----建立測試環境----建立循環
declare @a int
set @a=1
while(@a<>0)
begin
insert into #b select * from #a where patindex('%,%',id2)=0
delete ll from #a ll,#b where ll.id1=#b.id1
insert into b select id1,left(id2, patindex('%,%',id2)-1) from #a
update #a set id2=stuff(id2,1,patindex('%,%',id2),'')
select @a=count(*) from #a
end----刪除測試環境
select *from #a
drop table #a,#b
select * from b order by id1 ---b表為所求
insert into table1
select 1 , '1,2,3', ' name1,name2,name3'
-----------------------------------------------select top 0 * into #t from table1declare @id int
declare @count int
declare @n int
declare @m int
declare @k int
set @id=1
set @m=1
set @k=1
select @count=count(*) from table1
while @count>0
begin
select @n=len(cidstr)-len(replace(cidstr,',',''))+1 from table1 where id=@id
while @n>0
begin
insert into #t
select @id,
case when substring(cidstr,charindex(',',cidstr,@m)-1,1)='' then right(cidstr,1)
else substring(cidstr,charindex(',',cidstr,@m)-1,1) end as cidstr ,
case when substring(cnamestr,charindex(',',cnamestr,@k)-5,5)='' then right(cnamestr,5)
else substring(cnamestr,charindex(',',cnamestr,@k)-5,5) end as cnamestr from table1 where id=@id
set @m=@m+2
set @k=@k+7
set @n=@n-1
end
set @count=@count-1
set @id=@id+1
end
select * from #t