select * from news where left(id, len(id)) = left('1-2-2',len(id)) order by idcreate table news (
id char(10),
news char(20))insert news values ('1','新闻')
insert news values ('1-1','体育')
insert news values ('1-1-1','足球')
insert news values ('1-1-2','篮球')
insert news values ('1-2-1','电影')
insert news values ('1-2-2','电视')
insert news values ('1-2','娱乐')id news
---------- --------------------
1 新闻
1-2 娱乐
1-2-2 电视 (3 row(s) affected)
id char(10),
news char(20))insert news values ('1','新闻')
insert news values ('1-1','体育')
insert news values ('1-1-1','足球')
insert news values ('1-1-2','篮球')
insert news values ('1-2-1','电影')
insert news values ('1-2-2','电视')
insert news values ('1-2','娱乐')id news
---------- --------------------
1 新闻
1-2 娱乐
1-2-2 电视 (3 row(s) affected)
1
/ \
2 3
/ / \ / \ \
4 5 .. 8 9 10
我想根据新闻类的ID 10
得到树的导航 1>3>10
这种也怎么实现呢?
declare @mid varchar(10)
set @path = '10'
set @mid = '10'
while exists (select * from news where id = (select fid from news where id = @mid))
begin
select @mid=rtrim(fid) from news where id = @mid
set @path = @mid + '>' + @path
end
select @pathDROP table news
create table news (
id char(10),
fid char(10),
news char(20))insert news values ('1','0','新闻')
insert news values ('2','1','体育')
insert news values ('4','2','足球')
insert news values ('5','2','篮球')
insert news values ('8','3','电影')
insert news values ('9','3','电视')
insert news values ('10','3','歌舞')
insert news values ('3','1','娱乐')--------------------------------------------------
1>3>10(1 row(s) affected)
如果我想得到
id news
---------- --------------------
1 新闻
3 娱乐
110 电视
怎么实现?
declare @mid varchar(10)set @mid = '10'
select * into #t from news where id=10
while exists (select 1 from news a,#t b where a.id=b.fid and a.id not in (select id from #t ))
begin
insert #t select a.* from news a,#t b where a.id=b.fid and a.id not in (select id from #t )
endselect * from #t order by cast(id as int)