SQL 递归问题

Question

经典：
create table isc_catalog(nid int, npid int, ccategory char(10))
insert into isc_catalog values('1', '0', '中国')
insert into isc_catalog values('2', '1', '湖南省')
insert into isc_catalog values('3', '1', '广东省')
insert into isc_catalog values('4', '3', '广州市')
insert into isc_catalog values('5', '2', '长沙市')
insert into isc_catalog values('6', '4', '白云区')
insert into isc_catalog values('7', '5', '雨花区')我要查询出这种层次的结果，层次可以自己定义。
pLevel  pLeve2  pLeve3   pLeve4
中国    湖南省   长沙市   雨花区
中国    广东省   广州市   白云区邹建大哥，希望你能从百忙之中抽出时间帮我处理，谢谢！

Answer 1

这个问题,不是简单的替规问题.
比如 有个 xx市 ,它(假设的,你的数据怎么定义又是另一回事) 没有子级, 那么得到的行只有三列.
也即,一个没有子的节点,它呈现的行的结构跟别的会不同,即行集的结构就有不同,不会统一,就得不到行集.
除非pleve4只能给null.你确定是这样吗?

Answer 2

create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省') 
insert   into   isc_catalog   values('3',   '1',   '广东省') 
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区') go
create function F_test()
returns @T table (china  char(10),ID int identity,nid   int,   npid   int,   ccategory   char(10),lev int)
as
begin
declare @i int
set @i=0
insert @T select '中国',*,@i from isc_catalog where ccategory='中国'
while @@rowcount>0
begin
set @i=@i+1
insert @T 
select '中国',a.*,@i
from 
isc_catalog a
join
@T b on a.npid=b.nid
where
not exists(select 1 from @T where nid=a.nid)

end
return
endgodeclare @i int,@s nvarchar(4000)
select @i=max(lev)from dbo.F_test() group by lev
set @s=''
while @i>0
select @s=',[pLeve'+rtrim(@i)+']=max(case when lev='+rtrim(@i)+' then ccategory else ''''end )'+@s,@i=@i-1
--print @sexec('select
china'+@s+'
from 
(select 
*,con=(select count(1) from dbo.F_test() where lev=a.lev and ID!>a.ID)
from 
dbo.F_test() a)T
group by con,china')china      pLeve1     pLeve2     pLeve3     
---------- ---------- ---------- ---------- 
中国         湖南省        长沙市        白云区    
中国         广东省        广州市        雨花区    

Answer 3

生成一个递归的表
生成的语句：
select
china,[pLeve1]=max(case when lev=1 then ccategory else ''end ),[pLeve2]=max(case when lev=2 then ccategory else ''end ),[pLeve3]=max(case when lev=3 then ccategory else ''end )
from 
(select 
*,con=(select count(1) from dbo.F_test() where lev=a.lev and ID!>a.ID)
from 
dbo.F_test() a)T
group by con,china

Answer 4

没有子结点的(如北京市)，这样显示
pLevel     pLeve2     pLeve3       pLeve4 
中国         湖南省       长沙市       雨花区 
中国         广东省       广州市       白云区 
中国         北京市       北京市       北京市

Answer 5

create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省')  
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区')
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('3',   '1',   '广东省') go
create function F_test()
returns @T2 table (ID int identity,china  char(10), ccategory   char(10),lev int)
as
begin
declare @T table (china  char(10),nid   int,   npid   int,   ccategory   char(10),lev int,Code nvarchar(1000))
declare @i int
set @i=0
insert @T select '中国',*,@i,right(10000+nid,4) from isc_catalog where ccategory='中国'
while @@rowcount>0
begin
set @i=@i+1
insert @T 
select '中国',a.*,@i,b.Code+right(rtrim(10000+a.nid),4)
from 
isc_catalog a
join
@T b on a.npid=b.nid
where
not exists(select 1 from @T where nid=a.nid)

end
insert @T2 select china,ccategory,lev from @T order by Code
return
endgoselect *,ID=identity(int,1,1) into #
 from F_test()
go--select * from #select
    china,[pLeve1]=max(case when lev=1 then ccategory else ''end ),[pLeve2]=max(case when lev=2 then ccategory else ''end ),[pLeve3]=max(case when lev=3 then ccategory else ''end )
from 
    (select 
        *,con=(select count(1) from  # where lev=a.lev and ID!>a.ID)
    from 
         # a)T
group by con,chinachina      pLeve1     pLeve2     pLeve3     
---------- ---------- ---------- ---------- 
中国         湖南省        长沙市        雨花区    
中国         广东省        广州市        白云区    （所影响的行数为 2 行）

Answer 6

没有子结点的(如北京市)，这样显示 
看到了
--insert @T2 select china,ccategory,lev from @T order by Code
这一段解决结点排序问题

Answer 7

--改改函数：
create function F_test()
returns @T2 table (ID int,china  char(10), ccategory   char(10),lev int)
as
begin
declare @T table (china  char(10),nid   int,   npid   int,   ccategory   char(10),lev int,Code nvarchar(1000))
declare @i int
set @i=0
insert @T select '中国',*,@i,right(10000+nid,4) from isc_catalog where ccategory='中国'
while @@rowcount>0
begin
set @i=@i+1
insert @T 
select '中国',a.*,@i,b.Code+right(rtrim(10000+a.nid),4)
from 
isc_catalog a
join
@T b on a.npid=b.nid
where
not exists(select 1 from @T where nid=a.nid)

end
insert @T2 select 1,china,ccategory,lev from @T order by Code
set @i=0
update t
set ID=@i,@i=@i+1--这样生成自增
from 
@T2 t
return
end
go
select
    china,[pLeve1]=max(case when lev=1 then ccategory else ''end ),[pLeve2]=max(case when lev=2 then ccategory else ''end ),[pLeve3]=max(case when lev=3 then ccategory else ''end )
from 
    (select 
        *,con=(select count(1) from  F_test() where lev=a.lev and ID!>a.ID)
    from 
        F_test() a)T
group by con,china
china      pLeve1     pLeve2     pLeve3     
---------- ---------- ---------- ---------- 
中国         湖南省        长沙市        雨花区    
中国         广东省        广州市        白云区    （所影响的行数为 2 行）

Answer 8

drop table isc_catalog
drop function F_test
create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省') 
insert   into   isc_catalog   values('3',   '1',   '广东省') 
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区') 
insert   into   isc_catalog   values('8',   '2',   '娄底市')
insert   into   isc_catalog   values('9',   '1',   '北京市')
gocreate function F_test()
returns @T2 table (ID int,china  char(10), ccategory   char(10),lev int)
as
begin
    declare @T table (china  char(10),nid   int,   npid   int,   ccategory   char(10),lev int,Code nvarchar(1000))
    declare @i int
    set @i=0
    insert @T select '中国',*,@i,right(10000+nid,4) from isc_catalog where ccategory='中国'
    while @@rowcount>0
    begin
        set @i=@i+1
        insert @T 
        select '中国',a.*,@i,b.Code+right(rtrim(10000+a.nid),4)
        from 
            isc_catalog a
        join
            @T b on a.npid=b.nid
        where
            not exists(select 1 from @T where nid=a.nid)
    
    end
    insert @T2 select 1,china,ccategory,lev from @T order by Code
    set @i=0
    update t
    set ID=@i,@i=@i+1--这样生成自增
    from 
        @T2 t
    return
end
go
select
    china,[pLeve1]=max(case when lev=1 then ccategory else ''end ),[pLeve2]=max(case when lev=2 then ccategory else ''end ),[pLeve3]=max(case when lev=3 then ccategory else ''end )
from 
    (select 
        *,con=(select count(1) from  F_test() where lev=a.lev and ID!>a.ID)
    from 
        F_test() a)T
group by con,china
我增加了两条记录，就出现问题了

Answer 9

create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省')  
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区')
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('3',   '1',   '广东省') 
insert   into   isc_catalog   values('8',   '1',   '北京') 
goalter function F_test()
returns @T2 table (ID int,china  char(10), ccategory   char(10),lev int)
as
begin
    declare @T table (china  char(10),nid   int,   npid   int,   ccategory   char(10),lev int,Code nvarchar(1000))
    declare @i int
    set @i=0
    insert @T select '中国',*,@i,right(10000+nid,4) from isc_catalog where ccategory='中国'
    while exists(select 1 from isc_catalog a where not exists(select 1 from @t where nid=a.nid))
    begin
        set @i=@i+1
        insert @T 
        select 
'中国',
isnull(a.nid,b.nid),
isnull(a.npid,b.npid),
isnull(a.ccategory,b.ccategory),
@i,
b.Code+case when a.nid is null then b.Code else right(10000+a.nid,4) end
        from 
            isc_catalog a
        right join
            @T b on a.npid=b.nid 
where b.lev=@i-1    
    end
    insert @T2 select 1,china,ccategory,lev from @T order by Code
    set @i=0
    update t
    set ID=@i,@i=@i+1--这样生成自增
    from 
        @T2 t
    return
end
goselect
    china,[pLeve1]=max(case when lev=1 then ccategory else ''end ),[pLeve2]=max(case when lev=2 then ccategory else ''end ),[pLeve3]=max(case when lev=3 then ccategory else ''end )
from 
    (select 
        *,con=(select count(1) from  F_test() where lev=a.lev and ID!>a.ID)
    from 
        F_test() a)T
group by con,china

Answer 10

结果集:china      pLeve1     pLeve2     pLeve3     
---------- ---------- ---------- ---------- 
中国         湖南省        长沙市        雨花区    
中国         广东省        广州市        白云区    
中国         北京         北京         北京      （所影响的行数为 3 行）

Answer 11

drop table isc_catalog
drop function F_test
create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省') 
insert   into   isc_catalog   values('3',   '1',   '广东省') 
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区') 
insert   into   isc_catalog   values('8',   '2',   '娄底市')
insert   into   isc_catalog   values('9',   '1',   '北京市')
gocreate function F_test()
returns @T2 table (ID int,china  char(10), ccategory   char(10),lev int)
as
begin
    declare @T table (china  char(10),nid   int,   npid   int,   ccategory   char(10),lev int,Code nvarchar(1000))
    declare @i int
    set @i=0
    insert @T select '中国',*,@i,right(10000+nid,4) from isc_catalog where ccategory='中国'
    while exists(select 1 from isc_catalog a where not exists(select 1 from @t where nid=a.nid))
    begin
        set @i=@i+1
        insert @T 
        select 
            '中国',
            isnull(a.nid,b.nid),
            isnull(a.npid,b.npid),
            isnull(a.ccategory,b.ccategory),
            @i,
            b.Code+case when a.nid is null then b.Code else right(10000+a.nid,4) end
        from 
            isc_catalog a
        right join
            @T b on a.npid=b.nid 
    where b.lev=@i-1    
    end
    insert @T2 select 1,china,ccategory,lev from @T order by Code
    set @i=0
    update t
    set ID=@i,@i=@i+1--这样生成自增
    from 
        @T2 t
    return
endgo
运行后，结果如下：
china      pLeve1     pLeve2     pLeve3     
---------- ---------- ---------- ---------- 中国       湖南省     长沙市     雨花区    
中国       广东省     娄底市     娄底市    
中国       北京市     广州市     白云区    
中国                  北京市     北京市    结果有问题，正确的应该是：
china      pLeve1     pLeve2     pLeve3     
---------- ---------- ---------- ---------- 中国       湖南省     长沙市     雨花区    
中国       湖南省     娄底市     娄底市    
中国       广东省     广州市     白云区    
中国       北京市           北京市     北京市 

Answer 12

正确的应该是： 
china          pLeve1       pLeve2       pLeve3           
----------   ----------   ----------   ----------   中国             湖南省         长沙市         雨花区         
中国             湖南省         娄底市         娄底市         
中国             广东省         广州市         白云区         
中国             北京市         北京市         北京市   

Answer 13

再来一贴..
create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省') 
insert   into   isc_catalog   values('3',   '1',   '广东省') 
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区') 
insert   into   isc_catalog   values('8',   '2',   '娄底市')
insert   into   isc_catalog   values('9',   '1',   '北京市')
go
alter function F_test(@nid int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000),@npid int
select @s=rtrim(ccategory),@npid=npid from isc_catalog where nid=@nid
if @s is null
return null

return @s+isnull('.'+dbo.F_test(@npid),'')
end
goselect 
parseName(dbo.F_test(nid),1),
isnull(parseName(dbo.F_test(nid),2),parseName(dbo.F_test(nid),1)),
isnull(parseName(dbo.F_test(nid),3),parseName(dbo.F_test(nid),2)),
isnull(isnull(parseName(dbo.F_test(nid),4),parseName(dbo.F_test(nid),3)),parseName(dbo.F_test(nid),2))
from 
isc_catalog a  
where 
not exists(select 1 from isc_catalog where npid=a.nid)/*
中国 广东省 广州市 白云区
中国 湖南省 长沙市 雨花区
中国 湖南省 娄底市 娄底市
中国 北京市 北京市 北京市
*/

Answer 14

drop table isc_catalog
drop function F_test
create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省') 
insert   into   isc_catalog   values('3',   '1',   '广东省') 
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '天河区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区') 
insert   into   isc_catalog   values('8',   '2',   '娄底市')
insert   into   isc_catalog   values('9',   '1',   '北京市')
insert   into   isc_catalog   values('10',   '6',   '天河软件园')
go
create function F_test(@nid int)
returns nvarchar(1000)
as
begin
    declare @s nvarchar(1000),@npid int
    select @s=rtrim(ccategory),@npid=npid from isc_catalog where nid=@nid
    if @s is null
        return null
    
    return @s+isnull('.'+dbo.F_test(@npid),'')
end
goselect 
   parseName(dbo.F_test(nid),1),
    isnull(parseName(dbo.F_test(nid),2),parseName(dbo.F_test(nid),1)),
    isnull(parseName(dbo.F_test(nid),3),parseName(dbo.F_test(nid),2)),
    isnull(isnull(parseName(dbo.F_test(nid),4),parseName(dbo.F_test(nid),3)),parseName(dbo.F_test(nid),2))    
from 
    isc_catalog a  
where 
    not exists(select 1 from isc_catalog where npid=a.nid)/*
中国 湖南省 长沙市 雨花区
中国 湖南省 娄底市 娄底市
中国 北京市 北京市 北京市
NULL NULL NULL NULL*/我测试了，假如有五级，我只显示四级的话就有问题。

Answer 15

create table isc_catalog(nid int, npid int, ccategory char(10)) 
insert into isc_catalog values('1', '0', '中国') 
insert into isc_catalog values('2', '1', '湖南省') 
insert into isc_catalog values('5', '2', '长沙市') 
insert into isc_catalog values('6', '4', '白云区') 
insert into isc_catalog values('7', '5', '雨花区')
insert into isc_catalog values('4', '3', '广州市') 
insert into isc_catalog values('3', '1', '广东省') 
insert into isc_catalog values('8', '1', '北京') go
-- 叶子的路径
create function fn_path(@nid int)
returns varchar(128) as begin
declare @path varchar(128) set @path=''

select @path = ccategory, @nid = npid
from isc_catalog
where @nid = nid

while @nid > 0 begin
select @path = rtrim(ccategory)+','+@path, @nid = npid
from isc_catalog
where @nid = nid
end
return rtrim(@path)
end
go-- 叶子列表
select dbo.fn_path(nid)
from isc_catalog a
where not exists (select 1 from isc_catalog b
where b.npid = a.nid)go
drop function fn_path
drop table isc_catalog
go结果集：--------
中国,广东省,广州市,白云区
中国,湖南省,长沙市,雨花区
中国,北京
---------------
该方案不能满足楼主需求，但支持任意的层次级别，可以在前台应用中使用，根据","拆分数组。
自得一番 

Answer 16

create   table   isc_catalog(nid   int,   npid   int,   ccategory   nvarchar(10)) 
insert   into   isc_catalog   values('1',   '0',   N'中国') 
insert   into   isc_catalog   values('2',   '1',   N'湖南省') 
insert   into   isc_catalog   values('3',   '1',   N'广东省') 
insert   into   isc_catalog   values('4',   '3',   N'广州市') 
insert   into   isc_catalog   values('5',   '2',   N'长沙市') 
insert   into   isc_catalog   values('6',   '4',   N'天河区') 
insert   into   isc_catalog   values('7',   '5',   N'雨花区') 
insert   into   isc_catalog   values('8',   '2',   N'娄底市')
insert   into   isc_catalog   values('9',   '1',   N'北京市')
insert   into   isc_catalog   values('10',   '6',  N'天河软件园')
go--############################-
--采用公用表达式可以满足你的需求--
--############################-
BEGIN
DECLARE @npid int
SET @npid=0
DECLARE @lvl INT
--SET @lvl=5 
;WITH CatalogCTE(nid,ccategory,npid,lvl) --需要加分号(semicolon)
AS
(
  SELECT nid,ccategory,npid,0
  FROM isc_catalog
  WHERE npid = @npid
  UNION ALL
  SELECT N.nid, N.ccategory, N.npid ,M.lvl+1
  FROM isc_catalog as N
JOIN CatalogCTE as M ON N.npid = M.nid
  --WHERE lvl < @lvl
)
--SELECT nid,ccategory,npid,lvl FROM CatalogCTE WHERE lvl < @lvl
select a.ccategory N'国家',b.ccategory N'省/直辖市',
--case isnull(b.ccategory,'') when '' then a.ccategory else b.ccategory end N'省/直辖市',
case isnull(c.ccategory,'') when '' then b.ccategory else c.ccategory end N'市',
d.ccategory N'区'
from CatalogCTE a left join CatalogCTE b on a.nid=b.npid 
left join CatalogCTE c on b.nid=c.npid
left join CatalogCTE d on c.nid=d.npid
where a.nid=1
END
Godrop table isc_catalog

Answer 17

如果递归超过4层时，不能用parseName,只能自己写一个分拆。。
再举个例子create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省') 
insert   into   isc_catalog   values('3',   '1',   '广东省') 
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区') 
insert   into   isc_catalog   values('8',   '2',   '娄底市')
insert   into   isc_catalog   values('9',   '1',   '北京市')
insert   into   isc_catalog   values('10',   '6',   '白云小区A')--新增一条记录
go
create  function F_test(@nid int)
returns nvarchar(1000)
as
begin
    declare @s nvarchar(1000),@npid int
    select @s=rtrim(ccategory),@npid=npid from isc_catalog where nid=@nid
    if @s is null
        return null
    
    return isnull(dbo.F_test(@npid)+',','')+@s --用,分隔
--@s+isnull('.'+dbo.F_test(@npid),'')
end
go--加一个分隔函数:
create  function F_split(
@s varchar(8000),       --包含多个数据项的字符串
@pos int,              --要获取的数据项的位置
@split varchar(10)     --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int --分隔符长度
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND charindex(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')
RETURN(nullif(left(@s,charindex(@split,@s+@split)-1),''))
END
GOselect 
dbo.F_split(dbo.F_test(nid),1,','),
coalesce(dbo.F_split(dbo.F_test(nid),2,','),dbo.F_split(dbo.F_test(nid),1,',')),
coalesce(dbo.F_split(dbo.F_test(nid),3,','),dbo.F_split(dbo.F_test(nid),2,',')),
coalesce(dbo.F_split(dbo.F_test(nid),4,','),dbo.F_split(dbo.F_test(nid),3,','),dbo.F_split(dbo.F_test(nid),2,',')),
coalesce(dbo.F_split(dbo.F_test(nid),5,','),dbo.F_split(dbo.F_test(nid),4,','),dbo.F_split(dbo.F_test(nid),3,','),dbo.F_split(dbo.F_test(nid),2,','))  
from 
    isc_catalog a  
where 
    not exists(select 1 from isc_catalog where npid=a.nid)/*
中国 湖南省 长沙市 雨花区 雨花区
中国 湖南省 娄底市 娄底市 娄底市
中国 北京市 北京市 北京市 北京市
中国 广东省 广州市 白云区 白云小区A
*/

Answer 18

要是Oracle的话，那就相当简单了

Answer 19

if object_id('isc_catalog') is not null
drop table isc_catalog
gocreate   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中國') 
insert   into   isc_catalog   values('2',   '1',   '湖南省') 
insert   into   isc_catalog   values('3',   '1',   '廣東省') 
insert   into   isc_catalog   values('4',   '3',   '廣州市') 
insert   into   isc_catalog   values('5',   '2',   '長沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云區') 
insert   into   isc_catalog   values('7',   '5',   '雨花區') 
goif exists(select * from sysobjects where name='fn_calculate_level' and type='tf')
drop function fn_calculate_level
go
create function fn_calculate_level()
returns @temp table(link varchar(1000) null,nid int,ccategory char(10),level int)
as
begin
--create table @temp(link varchar(1000) null,nid int,level int)
declare @level int
set @level=1
insert @temp select cast(npid as varchar)+'.'+cast(nid as varchar),nid,ccategory,@level from isc_catalog where npid=0
while(@@rowcount>0)
begin
set @level=@level+1
insert @temp select b.link+'.'+cast(a.nid as varchar),a.nid,a.ccategory,@level from isc_catalog a join @temp b on a.npid=b.nid where b.level=@level-1
end --insert @re select * from @temp return
end
goselect * from isc_catalog
/*
nid         npid        ccategory  
----------- ----------- ---------- 
1           0           中國      
2           1           湖南省    
3           1           廣東省    
4           3           廣州市    
5           2           長沙市    
6           4           白云區    
7           5           雨花區    
*/
select (select ccategory from isc_catalog where nid=1) pLevel,max(case when a.level=2 then a.ccategory end) PLevel2,max(case when a.level=3 then a.ccategory end) PLevel3,max(case when a.level=4 then a.ccategory end) PLevel4 from fn_calculate_level() a join fn_calculate_level() b on a.link like b.link+'%' where b.level=2 group by b.link
/*
我要查询出这种层次的结果，层次可以自己定义。 
pLevel     PLevel2    PLevel3    PLevel4    
---------- ---------- ---------- ---------- 
中國         湖南省        長沙市        雨花區    
中國         廣東省        廣州市        白云區    
*/

Answer 20

 WITH n(npid, chain) AS 
           (SELECT nid, CAST(ccategory AS VARCHAR(50)) 
              FROM isc_catalog
              WHERE ccategory = '中国'
            UNION ALL
            SELECT nplus1.npid, 
                    n.chain || ':' || nplus1.ccategory 
              FROM emp as nplus1, n
              WHERE n.pid= nplus1.npid)
 SELECT chain FROM n
 ORDER BY chain;这是在DB2下写的SQL，递归查询，结果是地区名都连接成了一个字段。
没有运行，但结果应该是这样的：中國：湖南省：長沙市：雨花區        
中國：廣東省：廣州市：白云區 把这些地名分成列太难了，因为列数未知。写存储过程应该可以实现，不过我不会写，羞愧啊，呵呵。
ORACLE好像不支持SQL的递归查询，用CONNECT BY代替。

Answer 21

xiebinren 
 
等 级：
 发表于：2007-12-28 23:00:2950楼 得分:0 
期待中 
 
---------------
33楼已贴了答案..

Answer 22

select nvl(a.ccategory,' '),nvl(b.ccategory,' '),nvl(c.ccategory,' '),nvl(e.ccategory,' ')
from isc_catalog a, isc_catalog b, isc_catalog c, isc_catalog e
where (a.nid = 1 and b.npid = a.nid and c.npid (+) = b.nid and e.npid (+) = c.nid)

Answer 23

Select T1.CCategory,T2.CCategory,T3.CCategory,T4.CCategory
From isc_catalog T1 Inner Join isc_catalog T2 On (T1.Nid = T2.NPid)
Inner Join isc_catalog T3 On (T2.NId = T3.NPID)
Inner Join isc_catalog T4 On (T3.Nid = T4.Npid)
有那么复杂吗，这是一个比较常用的自联接！

Answer 24

一个自连接的问题，很简单嘛，自己看下SQL帮助文件就会了
create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省') 
insert   into   isc_catalog   values('3',   '1',   '广东省') 
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区') select t1.ccategory ,t2.ccategory,t3.ccategory,t4.ccategory
from isc_catalog t1 INNER JOIN isc_catalog t2 on t1.nid = t2.npid inner join isc_catalog t3 on t2.nid = t3.npid inner join isc_catalog t4 on t3.nid = t4.npid----------------------------------------------------------中国       广东省     广州市     白云区    
中国       湖南省     长沙市     雨花区    简单问题不要想得那么复杂

Answer 25

对于这个问题
我觉得自连接容易把程序写死了啊 
而递归则很灵活，不容易固定程序要几次连接还有，to：46楼
你说的递归效率低确实存在一个问题，不过在mssql中似乎ms给你做了优化，
再有，将数据存储在数据库上，设计好的话是可以避免递归的
但楼主的问题你能大概说说怎么设计能避免递归么，我没有太想好，请指点一下，谢谢！

Answer 26

层次受sqlserver 限制 不可以超过1022列if exists (select name from sysobjects where name ='isc_catalog' and xtype='U' )drop table isc_catalogcreate   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省') 
insert   into   isc_catalog   values('3',   '1',   '广东省') 
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区') 
insert   into   isc_catalog   values('8',   '2',   '娄底市')
insert   into   isc_catalog   values('9',   '1',   '北京市')
insert   into   isc_catalog   values('10',   '6',   '白云小区A')--新增一条记录
insert   into   isc_catalog   values('11',   '0',   '美国')--新增一条记录
insert   into   isc_catalog   values('12',   '0',   '欧盟')--新增一条记录
insert   into   isc_catalog   values('13',   '12',   '英国')--新增一条记录select nid,ccategory as r0 into #r from isc_catalog where npid =0 
declare @count as int
declare @count2 as int
declare @chrsql as varchar(8000)
set @count=1select * into #tmp from #r where 1=0
create index ix_r_nid on #R(nid)
create index ix_tmp_nid on #tmp(nid)
while exists(select a.nid from #r a left join isc_catalog b on a.nid=b.npid  where not b.nid is null )
begin
set @count2=@count-1 exec('insert into #tmp select * from #R')
set @chrsql=(' 
truncate table #r 
alter table #r  add  R'+convert(varchar(20),@count)+' char(10) 
alter table #r  add  idtmp int ')
execute(@chrsql)
set @chrsql=('insert into #r select a.* ,case when b.nid is null then a.R'
+convert(varchar(20),@count2)+' else  b.ccategory end  as R'+convert(varchar(20),@count)+' ,
case when b.nid is null then -1 else b.nid end as idtmp
from #tmp a left join isc_catalog b on a.nid=b.npid')
exec( @chrsql)
exec('update #r set nid=idtmp ')
exec('alter table #r drop column idtmp')
exec('truncate table #tmp')
set @chrsql=('alter table #tmp  add  R'+convert(varchar(20),@count)+' char(10) ')
execute(@chrsql) set @count=@count+1
end
exec('drop index #r.ix_r_nid
alter table #r drop column nid')
exec('select * from #r')
drop table #r
drop table #tmp

Answer 27

--什么递归呀，函数呀，都不用，直接一条动态语句就可以，最多支持80层，应该够用吧。
create   table   isc_catalog(nid   int,   npid   int,   ccategory   char(10)) 
insert   into   isc_catalog   values('1',   '0',   '中国') 
insert   into   isc_catalog   values('2',   '1',   '湖南省') 
insert   into   isc_catalog   values('3',   '1',   '广东省') 
insert   into   isc_catalog   values('4',   '3',   '广州市') 
insert   into   isc_catalog   values('5',   '2',   '长沙市') 
insert   into   isc_catalog   values('6',   '4',   '白云区') 
insert   into   isc_catalog   values('7',   '5',   '雨花区') 
insert   into   isc_catalog   values('8',   '2',   '娄底市')
insert   into   isc_catalog   values('9',   '1',   '北京市')
insert   into   isc_catalog   values('10',   '4',   '天河区')
insert   into   isc_catalog   values('11',   '0',   '美国')
declare @i int,@j int,@s varchar(8000)
select top 1 @j = npid from isc_catalog order by npid desc,nid desc
set @s = 'select nid id1,ccategory c1 from isc_catalog where npid = 0'
set @i = 2
while @j>0
 begin
  set @s = left(@s,charindex('nid',@s)-1)+'c'+rtrim(@i-1) + ',nid id'+rtrim(@i)+',isnull(ccategory,c'+rtrim(@i-1)+') c'+rtrim(@i)+ ' from ('+@s+') a'+rtrim(@i)+' left join isc_catalog on id'+rtrim(@i-1)+'= npid'
  set @i = @i +1
  select @j = npid from isc_catalog where nid = @j
 end
 set @s = left(@s,charindex('nid',@s)-1)+'c'+rtrim(@i-1)+' from ('+@s+') b'
exec(@s)
drop table isc_catalog
/*
c1         c2         c3         c4         
---------- ---------- ---------- ---------- 
中国         湖南省        长沙市        雨花区    
中国         湖南省        娄底市        娄底市    
中国         广东省        广州市        白云区    
中国         广东省        广州市        天河区    
中国         北京市        北京市        北京市    
美国         美国         美国         美国      
*/   

Answer 28

我个人觉得还是 roy_88 兄弟的比较灵活，因为可以轻松设置显示的层数
如四层：
c1         c2         c3         c4         
---------- ---------- ---------- ---------- 
中国         湖南省        长沙市        雨花区    
中国         湖南省        娄底市        娄底市    
中国         广东省        广州市        白云区    
中国         广东省        广州市        天河区    
中国         北京市        北京市        北京市    
美国         美国         美国         美国     
          
三层：
c1         c2         c3                
---------- ---------- ---------- 
中国         湖南省        长沙市            
中国         湖南省        娄底市            
中国         广东省        广州市            
中国         广东省        广州市            
中国         北京市        北京市            
美国         美国         美国