--查询示例 declare @re table(typeid int,cid int,level int) declare @l int set @l=0 insert @re select typeid,typeid,@l from 表a where roottypeid=0 while @@rowcount>0 begin set @l=@l+1 insert @re select a.typeid,b.typeid,@l from @re a,表a b where a.cid=b.roottypeid and a.level=@l-1 end select a.typeid,b.id,sid=0 into #t from @re a,表b b where a.cid=b.type order by a.typeid,b.id declare @typeid int update #t set @l=case when @typeid=typeid then @l+1 else 1 end ,sid=@l,@typeid=typeid select a.*,b.* from 表a a,表b b,#t c where a.typeid=c.typeid and b.type=c.cid and c.sid<=10 drop table #t
--修正鄒健的一點小錯誤 --創建測試環境 create table 表a( typeid int, typename varchar(50), roottypeid int) insert 表a select 1, '中國',0 union all select 2, '北京',1 union all select 3, '陝西',1 union all select 4, '海澱區',2 union all select 5, '朝陽區',2 create table 表b( id int ,title varchar(50),type int) insert 表b select 1, 'a1', 3 union all select 2, 'a2',3 union all select 3, 'b3',4 union all select 4, 'b4',5--查詢示例 declare @re table(typeid int,cid int,level int) declare @l int set @l=0 insert @re select typeid,typeid,@l from 表a where roottypeid=0 while @@rowcount>0 begin set @l=@l+1 insert @re select a.typeid,b.typeid,@l from @re a,表a b where a.cid=b.roottypeid and a.level=@l-1 end select a.typeid,b.id,sid=0 into #t from @re a,表b b where a.cid=b.type order by a.typeid,b.id declare @typeid int update #t set @l=case when @typeid=typeid then @l+1 else 1 end ,sid=@l,@typeid=typeid select a.*,b.* from 表a a,表b b,#t c where a.typeid=c.typeid and b.type=c.sid --此處鄒健打錯字了,不是c.cid and c.sid<=10 --刪除測試環境 drop table #t,表a ,表b --測試結果 typeid typename roottypeid id title type 1 中國 0 1 a1 3 1 中國 0 2 a2 3 1 中國 0 3 b3 4
select 表b.id,表b.title,表a.typeid,表a.typename from 表a,表b where typeid in ( select type from 表b group by type) and 表a.typeid=表b.type
修改一點點﹕ select 表b.id,表b.title,表b.type,表a.typename from 表a,表b where typeid in ( select type from 表b group by type) and 表a.typeid=表b.type 這樣就和你想要的結果一樣了﹗﹗﹗
declare @re table(typeid int,cid int,level int)
declare @l int
set @l=0
insert @re select typeid,typeid,@l
from 表a
where roottypeid=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.typeid,b.typeid,@l
from @re a,表a b
where a.cid=b.roottypeid and a.level=@l-1
end
select a.typeid,b.id,sid=0 into #t
from @re a,表b b
where a.cid=b.type
order by a.typeid,b.id
declare @typeid int
update #t set @l=case when @typeid=typeid then @l+1 else 1 end
,sid=@l,@typeid=typeid
select a.*,b.*
from 表a a,表b b,#t c
where a.typeid=c.typeid
and b.type=c.cid
and c.sid<=10
drop table #t
--創建測試環境
create table
表a(
typeid int, typename varchar(50), roottypeid int)
insert 表a
select
1, '中國',0 union all select
2, '北京',1 union all select
3, '陝西',1 union all select
4, '海澱區',2 union all select
5, '朝陽區',2 create table
表b(
id int ,title varchar(50),type int)
insert 表b
select
1, 'a1', 3 union all select
2, 'a2',3 union all select
3, 'b3',4 union all select
4, 'b4',5--查詢示例
declare @re table(typeid int,cid int,level int)
declare @l int
set @l=0
insert @re select typeid,typeid,@l
from 表a
where roottypeid=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.typeid,b.typeid,@l
from @re a,表a b
where a.cid=b.roottypeid and a.level=@l-1
end
select a.typeid,b.id,sid=0 into #t
from @re a,表b b
where a.cid=b.type
order by a.typeid,b.id
declare @typeid int
update #t set @l=case when @typeid=typeid then @l+1 else 1 end
,sid=@l,@typeid=typeid
select a.*,b.*
from 表a a,表b b,#t c
where a.typeid=c.typeid
and b.type=c.sid --此處鄒健打錯字了,不是c.cid
and c.sid<=10
--刪除測試環境
drop table #t,表a ,表b
--測試結果
typeid typename roottypeid id title type
1 中國 0 1 a1 3
1 中國 0 2 a2 3
1 中國 0 3 b3 4
where typeid in ( select type from 表b group by type) and 表a.typeid=表b.type
select 表b.id,表b.title,表b.type,表a.typename from 表a,表b
where typeid in ( select type from 表b group by type) and 表a.typeid=表b.type
這樣就和你想要的結果一樣了﹗﹗﹗