各位高人,求一条sql语句,多谢!

各位高人,求一条sql语句,谢谢!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
用一张表 table1 
结构是这样的 
id 名称 (shangjiid)上级id 级别 

1 国内新闻 0 1 
2 国际新闻 0 1 
3 财经新闻 1 2 
4 时政新闻 1 2 
5 股市行情 3 3 
6 期货行情 3 3 
7 财经新闻 2 2 
8 时政新闻 2 2 

求:如何将 国内新闻 下面的(财经新闻,时政新闻,股市行情期货行情)全部搜索出来 

我这里用一条sql语句但速度不快 
select id from table1 where id in(select id from table1 where shangjiid in( select id from table1 where shangjiid =(select id from table1 where lanmumingcheng='国内新闻'))) or id in(select id from table1 where shangjiid =(select id from table1 where lanmumingcheng='国内新闻')) 

谢谢各位高人了!!!!!!!!!!!!!!!!!!

------解决方案--------------------
遍历子节点
sql server 查找父节点及其所有子节点的函数

create table menu(
id int, --菜单id
name char(50), --菜单名称
parent int --父菜单id
)
insert into menu select
0, '爷爷' , null union all select
1, '爸爸', 0 union all select
2, '叔叔', 0 union all select
3, '大姑', 0 union all select
4, '小姑', 0 union all select
5, '我', 1 union all select
6, '亲弟', 1 union all select
7, '我儿子', 5 union all select
8, '我女儿', 5


******************************
--查找父节点的所有子节点的函数

create function f_getchildid(@id int)
returns @re table(id int)
as
begin
insert into @re select id from menu where parent=@id
while @@rowcount>0
insert into @re select a.id
from menu a inner join @re b on a.parent=b.id
where a.id not in(select id from @re)
return
end
go

--查找父节点本身及其所有子节点的函数

create function f_getchildid(@id int)
returns @re table(id int)
as

begin
insert @re select @id 
begin
insert into @re select id from menu where parent=@id
while @@rowcount>0
insert into @re select a.id
from menu a inner join @re b on a.parent=b.id
where a.id not in(select id from @re)
return
end
end
go


--调用示例,显示1的所有子.
select a.* from menu a inner join dbo.f_getchildid(1) b on a.id=b.id


转http://yangmeiwen.blog.163.com/blog/static/49304092008228105626145/


SQL SERVER中查找指定节点的所有子节点的示例函数
--查找指定节点的所有子节点的示例函数

--测试数据

CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))

INSERT tb SELECT '001',NULL ,'山东省'

UNION ALL SELECT '002','001','烟台市'

UNION ALL SELECT '004','002','招远市'

UNION ALL SELECT '003','001','青岛市'

UNION ALL SELECT '005',NULL ,'四会市'

UNION ALL SELECT '006','005','清远市'

UNION ALL SELECT '007','006','小分市'

GO

--查询指定节点及其所有子节点的函数

CREATE FUNCTION f_Cid(@ID char(3))

RETURNS @t_Level TABLE(ID char(3),Level int)

AS

BEGIN

DECLARE @Level int

SET @Level=1

INSERT @t_Level SELECT @ID,@Level

WHILE @@ROWCOUNT>0

BEGIN

SET @Level=@Level+1

INSERT @t_Level SELECT a.ID,@Level

FROM tb a,@t_Level b

WHERE a.PID=b.ID

AND b.Level=@Level-1

END

RETURN