SQL tree data struct(4): AdjList2MatPath (for printing tree struct)
来源:互联网 发布:win10修改mac地址 编辑:程序博客网 时间:2024/05/22 18:24
if object_id('proc_print_tree') is not null
drop procedure proc_print_tree
go
create procedure proc_print_tree @raw_data_sql nvarchar(1000), @debug bit = 0
with encryption
as
if object_id('tempdb..#adjacency_list') is not null
drop table #adjacency_list
create table #adjacency_list(code nvarchar(30) collate database_default, parent nvarchar(30) collate database_default)
if @debug = 1 print @raw_data_sql
insert #adjacency_list exec(@raw_data_sql)
if @debug = 1 select [data]='', * from #adjacency_list
if object_id('tempdb..#materialized_path') is not null
drop table #materialized_path
create table #materialized_path( code nvarchar(30) collate database_default, parent nvarchar(30) collate database_default,
height int, path nvarchar(3900) collate database_default)
declare @level int
select @level = 0
insert #materialized_path select code, null, 0, code from #adjacency_list where parent is null
while @@rowcount > 0
begin
select @level = @level + 1
insert into #materialized_path
select b.code, b.parent, @level, a.path + b.code
from #materialized_path a inner join #adjacency_list b on a.code = b.parent -- can not use left join!!!
where height = @level-1 and b.parent <> b.code
end
if @debug = 1 select [tree]='', * from #materialized_path order by path
if not exists(select * from #materialized_path)
begin
print 'no data exists in #materialized_path!'
return
end
declare @h int, @i int, @sql nvarchar(1000)
select @h = (select max(height) from #materialized_path), @i = 0, @sql = '0'
while @i <= @h
select @sql = @sql + ',case height when ' + cast(@i as varchar) +
' then code else '''' end as hierarchy_level_' + cast(@i as varchar) + char(13),
@i = @i + 1
set @sql = 'select ' + char(13) + substring(@sql, 3, 1000) + char(13) + ' from #materialized_path order by path'
if @debug = 1 print @sql
exec(@sql)
drop table #adjacency_list, #materialized_path
go
/*
print isnull(object_id('tempdb..#adjacency_list'), -1)
print isnull(object_id('tempdb..#temp'), -1)
exec proc_print_tree
'select distinct object_name(rkeyid), NULL
from sysreferences a
where not exists (select * from sysreferences b where a.rkeyid = b.fkeyid)
union all
select object_name(fkeyid), object_name(rkeyid) from sysreferences
'
exec proc_print_tree
'select ''APDocA'', NULL
union all
select object_name(fkeyid), object_name(rkeyid) from sysreferences
', 1
*/
- SQL tree data struct(4): AdjList2MatPath (for printing tree struct)
- SQL tree data struct(5): AdjList2NestedSet
- SQL tree data struct(6): NestedSet2AdjList
- SQL tree data struct(1): adjacency list (id, parentid)
- SQL tree data struct(2): materialized path (1.2.3...)
- SQL tree data struct(7): find the minimal cost route
- SQL tree data struct(3): nested set (1,12) include (2,3) & (4, 11) ...
- STRUCT DATA
- Data Struct
- struct tvec_base_t -- data structures for dynamic timers
- bool isBST(struct tree *root)
- struct的data对齐
- Data Struct \ Stack
- camera: V4l2 data struct
- Data Struct - Book
- 【Data Struct】单链表
- Struct
- struct
- SQL tree data struct(3): nested set (1,12) include (2,3) & (4, 11) ...
- 优秀课件笔记之面向对象操作系统的设计
- 数值图形处理软件"活图"简介
- 问问你自己
- 学好英语的42个经典要诀(完整版)
- SQL tree data struct(4): AdjList2MatPath (for printing tree struct)
- 新开的空间,大家来看看.
- 软件工程案例学习-图书管理系统
- SQL tree data struct(5): AdjList2NestedSet
- SQLServer保存ASP.NET Session状态
- 软件工程案例学习-网上购书系统
- SQL tree data struct(6): NestedSet2AdjList
- 优秀课件笔记之视听巧记汉英成语
- PMP之路-第81天