1,分解字符串
--N要连继,
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c---------------------------------------------------------------------
declare @a table (id int,string varchar(8000))
insert @a select 1 ,'a,b,c,sd,dfsdfg'
union select 2, 'a,n,sdf,we,t'
union select 3, 's,df,df'select a.*,b.*,id,substring(','+string+',',N+1,charindex(',',','+string+',',N+1)-(N+1))
from @a a,numtab b
where substring(','+string+',',N,8000) like ',_%'
order by id,N
--N要连继,
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c---------------------------------------------------------------------
declare @a table (id int,string varchar(8000))
insert @a select 1 ,'a,b,c,sd,dfsdfg'
union select 2, 'a,n,sdf,we,t'
union select 3, 's,df,df'select a.*,b.*,id,substring(','+string+',',N+1,charindex(',',','+string+',',N+1)-(N+1))
from @a a,numtab b
where substring(','+string+',',N,8000) like ',_%'
order by id,N
declare @li_i int
declare @li_id int
declare @li_level int
declare @ls_find varchar(400)
declare @ls_str varchar(100) set @li_i = 0
set @li_level = 0
--f、C字段分别记录在字符串中出现的位置,k记录所查找的字符串
select top 0 @li_i as id,@ls_find as findkey,@li_i as f,@li_i as c,@ls_str as k
into #t
set @ls_find = '卡通图案,植物,食物,枇杷,'
set @ls_find = reverse(@ls_find)
set @ls_find = right(@ls_find,len(@ls_find)-1)+','
print @ls_find
--产生排序字段f
set @li_i = charindex(',',@ls_find)
while @li_i > 0
begin
set @li_level = @li_level + 1
set @ls_str = left(@ls_find,@li_i-1)
set @ls_find = right(@ls_find,len(@ls_find)-@li_i)
print @ls_str
insert into #t (id ,findkey,f,c,k)
select id,findkey,@li_level,0,reverse(@ls_str)
from table1
where id not in (select id from #t)
and reverse(findkey) like '%,'+@ls_str+'%'
set @li_i = charindex(',',@ls_find)
end select * from #t
order by f,c --产生排序字段c
update #t set
findkey = reverse(findkey),
k = reverse(k) declare t_cursor cursor for
select left(findkey,charindex(','+k,findkey)),id
from #t open t_cursor
fetch next from t_cursor into @ls_find,@li_id
while @@fetch_status = 0
begin
set @li_level = 0
set @li_i = charindex(',',@ls_find)
while @li_i > 0
begin
set @li_level = @li_level + 1
set @ls_find = right(@ls_find,len(@ls_find)-@li_i)
set @li_i = charindex(',',@ls_find)
end
update #t set
c = @li_level
where id = @li_id
fetch next from t_cursor into @ls_find,@li_id
end close t_cursor
deallocate t_cursor update #t set
findkey = reverse(findkey),
k = reverse(k)
select * from #t
order by f,c
rollback tran
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLE1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLE1]
GOCREATE TABLE [dbo].[TABLE1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[findkey] [varchar] (400) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--(2003-6-17 19:47:13)表 TABLE1数据量 18
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,小孩,婴儿,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,小孩,婴儿,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,小孩,婴儿,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,小孩,女孩,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,小孩,女孩,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,小孩,女孩,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,小孩,女孩,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,小孩,男孩,女孩,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,小孩,男孩,女孩,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,小孩,男孩,女孩,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,运动,游泳,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '人物,运动,划船,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '植物,果实,苹果,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '植物,果实,桃李,桃子,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '植物,果实,枇杷,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '太空,航天,科技,宇宙飞船,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '太空,航天,科技,宇宙飞船,' )
INSERT INTO TABLE1 ( findkey ) VALUES ( '太空,航天,科技,火箭,' ) declare @li_i int
declare @li_id int
declare @li_level int
declare @ls_find varchar(400)
declare @ls_str varchar(100) set @li_i = 0
set @li_level = 0
--f、C字段分别记录在字符串中出现的位置,k记录所查找的字符串
select top 0 @li_i as id,@ls_find as findkey,@li_i as f,@li_i as c,@ls_str as k
into #t
set @ls_find = '卡通图案,植物,食物,枇杷,'
set @ls_find = reverse(@ls_find)
set @ls_find = right(@ls_find,len(@ls_find)-1)+','
print @ls_find
--产生排序字段f
set @li_i = charindex(',',@ls_find)
while @li_i > 0
begin
set @li_level = @li_level + 1
set @ls_str = left(@ls_find,@li_i-1)
set @ls_find = right(@ls_find,len(@ls_find)-@li_i)
print @ls_str
insert into #t (id ,findkey,f,c,k)
select id,findkey,@li_level,0,reverse(@ls_str)
from table1
where id not in (select id from #t)
and reverse(findkey) like '%,'+@ls_str+'%'
set @li_i = charindex(',',@ls_find)
end select * from #t
order by f,c --产生排序字段c
update #t set
findkey = reverse(findkey),
k = reverse(k) declare t_cursor cursor for
select left(findkey,charindex(','+k,findkey)),id
from #t open t_cursor
fetch next from t_cursor into @ls_find,@li_id
while @@fetch_status = 0
begin
set @li_level = 0
set @li_i = charindex(',',@ls_find)
while @li_i > 0
begin
set @li_level = @li_level + 1
set @ls_find = right(@ls_find,len(@ls_find)-@li_i)
set @li_i = charindex(',',@ls_find)
end
update #t set
c = @li_level
where id = @li_id
fetch next from t_cursor into @ls_find,@li_id
end close t_cursor
deallocate t_cursor update #t set
findkey = reverse(findkey),
k = reverse(k) select a.*
from table1 a,#t b
where a.id = b.id
order by f,c
rollback tran
ID 自身编号,主键
fID 父类编号,该类属于哪一个父类下的,就记录父类的编号,为顶层则为0
Value 值,记录名称
ID fID Value
-----------------------------
1 0 人物
2 0 植物
3 0 太空
4 1 小孩
5 1 运动
6 2 果实
7 3 航天
8 1 男孩
9 1 女孩... ...
建表:也可以从图形界面创建
Create Table YourTable (
ID int Identity(1,1) Primary Key,
fID int,
Value nvarchar(50)
)
----------------------------------------------------------
代码:
Create Procedure procGetSimilarWords(
String nvarchar(50)
)
As
Declare tempTable table (
ID int Primary Key,
Value nvarchar(150)
)
Declare @CurrentID int
Declare @Value nvarchar(150)Select @Value = Value,
@CurrentID = fID
From YourTable
Where @Value = @ValueWhile @CurrentID <> 0 Begin Select @Value = Value + ',' + @Value
@CurrentID = fID
From YourTable
Where ID = @CurrentIDEndInsert @tempTable(ID,Value)
Values(@ID,@Value)Select * from @tempTable
hillhx(曾经的曾经) 的方法我试了,能实现我的要求,就是速度有点慢:(