表名:kind
字段:id int, kind_id varchar(50), parent_id int(根id,0说明此为一级目录)有以下数据,供大家测试,这是个目录
__________________________________________________
ID Kind_ID Parent_ID
1 .1. 0
2 .1.2. 1
3 .1.2.3. 2
4 .1.2.3.4. 3
6 .6. 0
5 .6.5. 6
7 .1.2.7. 2
___________________________________________________
我想要的结果,当你输入一个表中存在的ID,
要求:一、如果是一级目录就只显示这个目录;如果非一级目录,显示其上的所有目录及其下一级目录
二、显示突出当前目录ID当选择ID=1 RESULT: 1ID=2
RESULT:[1]2[3][7]
字段:id int, kind_id varchar(50), parent_id int(根id,0说明此为一级目录)有以下数据,供大家测试,这是个目录
__________________________________________________
ID Kind_ID Parent_ID
1 .1. 0
2 .1.2. 1
3 .1.2.3. 2
4 .1.2.3.4. 3
6 .6. 0
5 .6.5. 6
7 .1.2.7. 2
___________________________________________________
我想要的结果,当你输入一个表中存在的ID,
要求:一、如果是一级目录就只显示这个目录;如果非一级目录,显示其上的所有目录及其下一级目录
二、显示突出当前目录ID当选择ID=1 RESULT: 1ID=2
RESULT:[1]2[3][7]
declare @Kind_IDtest varchar(100)
declare @iID int
declare @iIDtmp intset @Kind_IDtest = ''
set @iID = 2
set @iIDtmp = 2 IF EXISTS (SELECT * FROM kind WHERE ID = @iID AND Parent_ID <> 0)
BEGIN
set @iIDtmp = (SELECT Parent_ID FROM kind WHERE ID = @iID AND Parent_ID <> 0)
set @Kind_IDtest = @Kind_IDtest + (SELECT Kind_ID FROM kind WHERE ID = @iIDtmp)
ENDset @Kind_IDtest = @Kind_IDtest + (SELECT Kind_ID FROM kind WHERE ID = @iID)print @Kind_IDtest
insert @t
select 1,'.1.',0 union all
select 2,'.1.2.',1 union all
select 3,'.1.2.3.',2 union all
select 4,'.1.2.3.4.',3 union all
select 6,'.6.',0 union all
select 5,'.6.5.',6 union all
select 7,'.1.2.7.',2select b.*
from @t b
where charindex(cast(b.parent_id as varchar(10)),
(select case parent_id when 0 then '0' else kind_id end from @t where id = 6) ) >0
and
id <= (select case parent_id when 0 then id else 1000 end from @t where id = 6)
and
id >= (select case parent_id when 0 then id else 0 end from @t where id = 6)
/*
ID Kind_ID parent_ID
----------- -------------------------------------------------- -----------
6 .6. 0*/
select b.*
from @t b
where charindex(cast(b.parent_id as varchar(10)),
(select case parent_id when 0 then '0' else kind_id end from @t where id = 1) ) >0
and
id <= (select case parent_id when 0 then id else 1000 end from @t where id = 1)
and
id >= (select case parent_id when 0 then id else 0 end from @t where id = 1)
/*
ID Kind_ID parent_ID
----------- -------------------------------------------------- -----------
1 .1. 0
*/
select b.*
from @t b
where charindex(cast(b.parent_id as varchar(10)),
(select case parent_id when 0 then '0' else kind_id end from @t where id = 2) ) >0
and
id <= (select case parent_id when 0 then id else 1000 end from @t where id =2)
and
id >= (select case parent_id when 0 then id else 0 end from @t where id = 2)
/*
ID Kind_ID parent_ID
----------- -------------------------------------------------- -----------
2 .1.2. 1
3 .1.2.3. 2
7 .1.2.7. 2
*/
select 1,'.1.',0
union select 2,'.1.2.',1
union select 3,'.1.2.3.',2
union select 4,'.1.2.3.4.',3
union select 6,'.6.',0
union select 5,'.6.5.',6
union select 7,'.1.2.7.',2declare @a int -- 输入参数
set @a = 2
declare @i int
set @i = 1 -- 循环变量
declare @s nvarchar(50)
declare @f nvarchar(50)
set @s = ''
set @f = ''while @i <> 0
begin
set @i = isnull((select top 1 id from @test where parent_id = @a and id > @i),0)
set @s = ltrim(str(@i))
if @i <> 0
begin
set @f = @f + '[' + @s + ']'
end
end
select 目录 = case parent_id when 0 then ltrim(str(id))
else '[' + (select ltrim(str(parent_id)) from @test where id = @a) + ']' + ltrim(str(id)) + @f
end from @test where id = @a
Create table test (id int, kind_id varchar(50), parent_id int)insert into test(id,kind_id,parent_id)
select 1,'.1.',0
union select 2,'.1.2.',1
union select 3,'.1.2.3.',2
union select 4,'.1.2.3.4.',3
union select 6,'.6.',0
union select 5,'.6.5.',6
union select 7,'.1.2.7.',2if not object_id('GetRecords') is null
drop function GetRecords
gocreate function GetRecords(@str varchar(2000))
returns @Rec table (Record varchar(40))
as
begin
declare @s varchar(2000)
declare @r varchar(2000)
declare @i varchar(2000)
set @s=@str
set @i=CHARINDEX('.',@s)
while @i>0
begin
set @r=left(@s,@i-1)
if not exists(select 1 from @rec where record=@r)
insert @Rec values (@R)
set @s=right(@s,len(@s)-@i)
set @i=CHARINDEX('.',@s)
end
if len(@s)>0
if not exists(select 1 from @rec where record=@s)
insert @Rec values (@s) return
end
goDeclare @str varchar(8000)
Declare @result varchar(8000)
Declare @ID int
set @id=2
set @str=''
set @result=''
select @str=@str+left(rtrim(kind_id),len(Kind_id)-1) from test
where parent_id=@id
select @str=right(ltrim(@str),len(@str)-1)
select @result=@result+case record when cast(@id as varchar) then record else '['+record+']' end from dbo.GetRecords(@str)
select @result
select 1,'.1.',0
union select 2,'.1.2.',1
union select 3,'.1.2.3.',2
union select 4,'.1.2.3.4.',3
union select 6,'.6.',0
union select 5,'.6.5.',6
union select 7,'.1.2.7.',2declare @a int -- 输入参数
set @a = 2
declare @s varchar(2000)
set @s=@a
/*其下一级目录*/
select @s=@s+'['+convert(varchar,id)+']' from @tb where parent_id=@a/*其上的所有目录*/
declare @parent_id int
select @parent_id=parent_id from @tb where id=@a
while (@parent_id>0)
select @s='['+convert(varchar,@parent_id)+']'+@s,@parent_id=parent_id from @tb where id=@parent_id
select @s
应该在/*其下一级目录*/
中加入这样一句if ((select parent_id from @tb where id=@a)<>0)
select @s=@s+'['+convert(varchar,id)+']' from @tb where parent_id=@a就对了!
谢谢大家的帮助和启发,问题解决了!接分哈!