28,409
社区成员




--> 测试数据: catagories
if object_id('catagories') is not null drop table catagories
create table catagories (id int,name varchar(4),sub int)
insert into catagories
select 1,'欧洲',0 union all
select 2,'亚洲',0 union all
select 3,'中国',2 union all
select 4,'日本',2 union all
select 5,'韩国',2 union all
select 6,'英国',1
--> 测试数据: articles
if object_id('articles') is not null drop table articles
create table articles (id int,title varchar(8),catagories int)
insert into articles
select 1,'大阪之旅',4 union all
select 2,'首尔之旅',5 union all
select 3,'东京旅',4 union all
select 4,'走遍亚洲',2 union all
select 5,'走遍欧洲',1
go
create proc p_wsp
as
with wsp
as
(
select * from catagories where id=2
union all
select a.* from catagories a,wsp b where a.sub=b.id
)
select b.* from wsp a,articles b where a.id=b.catagories
go
exec p_wsp
--结果:
id title catagories
----------- -------- -----------
1 大阪之旅 4
2 首尔之旅 5
3 东京旅 4
4 走遍亚洲 2