这种格式的结果，高手帮忙。

vovo2000 2006-05-05 09:41:36

表的内容如下
sysid subid
----------- -----------
1 0
2 1
3 2
4 0
5 4
6 4
7 5
8 5
9 6
10 6

我想实现:
sys subsys_1 subsys_2
----- ---------- ------------
1 2 3
4 5 7
8
6 9
10
请教高手！

...全文

262 20 打赏收藏转发到动态举报

写回复

用AI写文章

20 条回复

切换为时间正序

请发表友善的回复…

发表回复

云中客 2006-05-06

打赏
举报

你的表结构设计的有问题,所以大家在开始时都不知道你是要做什么
如果想修改表结构的话,可以发信息给我

chenqianlong 2006-05-06

打赏
举报

create table #t(sysid int,subid int)
insert #t
select 1,0 union all
select 2,1 union all
select 3,2 union all
select 4,0 union all
select 5,4 union all
select 6,4 union all
select 7,5 union all
select 8,5 union all
select 9,6 union all
select 10,6

select * into #t1 from #t where subid=0
select k.*,k1.sysid csysid from
(select a.sysid,b.sysid as bsysid
from #t1 a,#t b
where a.sysid=b.subid) k,#t k1
where k.bsysid=k1.subid
drop table #t
drop table #t1
結果
sysid bsysid csysid
1 2 3
4 5 7
4 5 8
4 6 9
4 6 10

vovo2000 2006-05-06

打赏
举报

恩是可以的。但是过程相当麻烦，用了3个游标。
应该是有更巧妙的方法的，我曾经在查其他资料的时候，跟这个方法擦肩而过，现在需要的时候去找不着了。好像用到了交叉表的思想。
继续等待高手中。

Yang_ 2006-05-06

打赏
举报

没解决？

create table t(sysid int,subid int)
go

insert t
select 1,0 union all
select 2,1 union all
select 3,2 union all
select 4,0 union all
select 5,4 union all
select 6,4 union all
select 7,5 union all
select 8,5 union all
select 9,6 union all
select 10,6
go

select a.sysid as sys,b.sysid as subsys_1 into #t from t a,t b where a.subid=0
and b.subid=a.sysid

select a.*,b.sysid subsys_2
into #t1
from #t a,t b
where a.subsys_1=b.subid

select
case when not exists (select 1 from #t1 where sys=a.sys and (subsys_1<a.subsys_1 or subsys_1=a.subsys_1 and subsys_2<a.subsys_2))
then sys else null end as sys,
case when not exists (select 1 from #t1 where sys=a.sys and subsys_1=a.subsys_1 and subsys_2<a.subsys_2)
then subsys_1 else null end as subsys_1,
subsys_2
from #t1 a
order by a.sys,a.subsys_1,a.subsys_2

drop table #t
drop table #t1

李雅勇的技术专栏 2006-05-05

打赏
举报

顶了顶了

gulf1234 2006-05-05

打赏
举报

关注

Ripple_wang 2006-05-05

打赏
举报

declare @sysid varchar(2)
declare @sysid2 varchar(2)
declare @sysid3 varchar(2)

declare @sysid_t varchar(2)
declare @sysid2_t varchar(2)
declare @sysid3_t varchar(2)
set @sysid_t=' '
set @sysid2_t=' '
set @sysid3_t=' '
declare temp_cursor cursor for
select sysid from tt
where subid=0
open temp_cursor
fetch next from temp_cursor
into @sysid
WHILE @@FETCH_STATUS = 0
begin
--print @sysid
--------------
declare temp_cursor2 cursor for
select sysid from tt
where subid=@sysid
open temp_cursor2
fetch next from temp_cursor2
into @sysid2
WHILE @@FETCH_STATUS = 0
begin
declare temp_cursor3 cursor for
select sysid from tt
where subid=@sysid2
open temp_cursor3
fetch next from temp_cursor3
into @sysid3
WHILE @@FETCH_STATUS = 0
begin
--print @sysid+'*************'+ @sysid2+'*************' +@sysid3
if(@sysid_t<>@sysid )
begin
insert into tt2
values(@sysid,@sysid2,@sysid3)
end
else if(@sysid2_t<>@sysid2)
begin
insert into tt2
values(' ',@sysid2,@sysid3)
end
else
begin
insert into tt2
values(' ',' ',@sysid3)
end

set @sysid_t=@sysid
set @sysid2_t=@sysid2
set @sysid3_t=@sysid3

fetch next from temp_cursor3
into @sysid3
end
CLOSE temp_cursor3
DEALLOCATE temp_cursor3
------
fetch next from temp_cursor2
into @sysid2
end
CLOSE temp_cursor2
DEALLOCATE temp_cursor2
--------------
--select sysid from tt
--where subid=@sysid

fetch next from temp_cursor
into @sysid
end

CLOSE temp_cursor
DEALLOCATE temp_cursor

go
----for test---
select *
from tt2
---------
其中表tt(sysid,subid)
表tt1(sys,subsys_1,subsys_2)
前提:此樹形最多三層

edp08 2006-05-05

打赏
举报

结果不符合楼主要求呵：

(1)
| (2)
| | (3)
(4)
| (5)
| | (7)
| | (8)
| (6)
| | (9)
| | (10)

(10 row(s) affected)

edp08 2006-05-05

打赏
举报

做完才发现看错题了：

create table tree(sysid int, subid int)
insert tree select 1, 0
union
select 2, 1
union
select 3, 2
union
select 4, 0
union
select 5, 4
union
select 6, 4
union
select 7, 5
union
select 8, 5
union
select 9, 6
union
select 10, 6

WITH TreeCTE(sysid, subid, lvl, sortcol)
AS
(
SELECT sysid, subid, 0, CAST(sysid AS VARBINARY(900))
FROM tree
WHERE subid = 0
UNION ALL
SELECT E.sysid, E.subid, M.lvl+1, CAST(sortcol + CAST(E.sysid AS BINARY(4)) AS VARBINARY(900))
FROM tree AS E JOIN TreeCTE AS M ON E.subid = M.sysid
)
SELECT
REPLICATE(' | ', lvl)
+ '(' + (CAST(sysid AS VARCHAR(10))) + ') '
FROM TreeCTE
ORDER BY sortcol

drop table tree

Yang_ 2006-05-05