这个代码比较长,我只贴一些测试内容和个人评价,详细代码参考blog原文
http://blog.csdn.net/jinjazz/archive/2009/04/28/4133838.aspx树结构广泛用在各类分级管理设计中。但他的展现方式也是很让人头疼的事情。比如展开一个靠id和parentid建立关系的分级树,SQL2005已经可以用CTE来递归查询。我们看如下测试代码:
set nocount on
print '--SQL2005 CTE 树结构测试
'
declare @t table(id varchar(10) ,pid varchar(10), name varchar(10))insert into @t values('a',null,'000')
insert into @t values('b','a','111')
insert into @t values('c','b','222')
insert into @t values('d','b','333')
insert into @t values('f','c','444')
insert into @t values('e','c','555')
;with t (id, name, pid, path)
as(
select a.id, a.name, a.pid, cast(a.id as varchar(20)) as path
from @t as a
where pid is null
union all
select a.id, a.name, a.pid, cast(path +'>'+a.id as varchar(20))
from @t a
join t as b
on a.pid = b.id
)
select * from t /*
--SQL2005 CTE 树结构测试
id name pid path
---------- ---------- ---------- --------------------
a 000 NULL a
b 111 a a>b
c 222 b a>b>c
d 333 b a>b>d
f 444 c a>b>c>f
e 555 c a>b>c>e
*/
set nocount off当你还不知道这个写法的时候可能会有点激动,但是别忘了他是递归,性能并不比以前的循环+表变量好。我用他展开一个26000条数据,最深5级的权限组分级表时,10分钟后仍然没有查询结束。而我尝试用CLR写扩展函数来实现同样的效果,展开同样的结构,只需要不到2秒.clr的实现和部署代码这里略去,可以从我的blog原文获取
http://blog.csdn.net/jinjazz/archive/2009/04/28/4133838.aspx....这个CLR包括4个函数,两个负责维护并发key,一个负责建立链表,一个负责返回任意节点的路径。测试sql语句如下
set nocount on
print '--SQL2005 CLR 树结构测试
'
declare @t table(id varchar(10) ,pid varchar(10), name varchar(10))insert into @t values('a',null,'000')
insert into @t values('b','a','111')
insert into @t values('c','b','222')
insert into @t values('d','b','333')
insert into @t values('f','c','444')
insert into @t values('e','c','555')declare @key varchar(40) , @b bit
set @key=newid()
select @b=dbo.xfn_inittreekey(@key)
select @b=dbo.xfn_treebuilder(@key,id,pid) from @t
select *,cast(dbo.xfn_gettreepath(@key,id)as varchar(20)) as path from @t
select @b=dbo.xfn_disposetreekey(@key)
go
/*
--SQL2005 CLR 树结构测试
id pid name path
---------- ---------- ---------- --------------------
a NULL 000 a
b a 111 a>b
c b 222 a>b>c
d b 333 a>b>d
f c 444 a>b>c>f
e c 555 a>b>c>e
*/
set nocount off这个CLR函数的速度快是因为他牺牲了一部分空间节省了时间,只需要两次全表扫描,时间复杂度应该是O(n)级别的,所以比递归快了很多。目前我还没有发现可以不用提前建立树节点链表集合的方法,所以除了需要调用维护key的两个函数外,还需要调用xfn_treebuilder和xfn_gettreepath这两个clr函数。
http://blog.csdn.net/jinjazz/archive/2009/04/28/4133838.aspx树结构广泛用在各类分级管理设计中。但他的展现方式也是很让人头疼的事情。比如展开一个靠id和parentid建立关系的分级树,SQL2005已经可以用CTE来递归查询。我们看如下测试代码:
set nocount on
print '--SQL2005 CTE 树结构测试
'
declare @t table(id varchar(10) ,pid varchar(10), name varchar(10))insert into @t values('a',null,'000')
insert into @t values('b','a','111')
insert into @t values('c','b','222')
insert into @t values('d','b','333')
insert into @t values('f','c','444')
insert into @t values('e','c','555')
;with t (id, name, pid, path)
as(
select a.id, a.name, a.pid, cast(a.id as varchar(20)) as path
from @t as a
where pid is null
union all
select a.id, a.name, a.pid, cast(path +'>'+a.id as varchar(20))
from @t a
join t as b
on a.pid = b.id
)
select * from t /*
--SQL2005 CTE 树结构测试
id name pid path
---------- ---------- ---------- --------------------
a 000 NULL a
b 111 a a>b
c 222 b a>b>c
d 333 b a>b>d
f 444 c a>b>c>f
e 555 c a>b>c>e
*/
set nocount off当你还不知道这个写法的时候可能会有点激动,但是别忘了他是递归,性能并不比以前的循环+表变量好。我用他展开一个26000条数据,最深5级的权限组分级表时,10分钟后仍然没有查询结束。而我尝试用CLR写扩展函数来实现同样的效果,展开同样的结构,只需要不到2秒.clr的实现和部署代码这里略去,可以从我的blog原文获取
http://blog.csdn.net/jinjazz/archive/2009/04/28/4133838.aspx....这个CLR包括4个函数,两个负责维护并发key,一个负责建立链表,一个负责返回任意节点的路径。测试sql语句如下
set nocount on
print '--SQL2005 CLR 树结构测试
'
declare @t table(id varchar(10) ,pid varchar(10), name varchar(10))insert into @t values('a',null,'000')
insert into @t values('b','a','111')
insert into @t values('c','b','222')
insert into @t values('d','b','333')
insert into @t values('f','c','444')
insert into @t values('e','c','555')declare @key varchar(40) , @b bit
set @key=newid()
select @b=dbo.xfn_inittreekey(@key)
select @b=dbo.xfn_treebuilder(@key,id,pid) from @t
select *,cast(dbo.xfn_gettreepath(@key,id)as varchar(20)) as path from @t
select @b=dbo.xfn_disposetreekey(@key)
go
/*
--SQL2005 CLR 树结构测试
id pid name path
---------- ---------- ---------- --------------------
a NULL 000 a
b a 111 a>b
c b 222 a>b>c
d b 333 a>b>d
f c 444 a>b>c>f
e c 555 a>b>c>e
*/
set nocount off这个CLR函数的速度快是因为他牺牲了一部分空间节省了时间,只需要两次全表扫描,时间复杂度应该是O(n)级别的,所以比递归快了很多。目前我还没有发现可以不用提前建立树节点链表集合的方法,所以除了需要调用维护key的两个函数外,还需要调用xfn_treebuilder和xfn_gettreepath这两个clr函数。
解决方案 »
- 求教一个sql语句的写法
- SQL问题求助,查询结果如何清除NULL的空值?
- 一个简单的表,有两列,编号和姓名,其中编号是主键,我的目的是修改前十名员工的姓名(按编号排序)
- Sql Server 变态问题求救(删除数据库同名用户的问题)[给分帖,zjcxc(邹建) 请进][内有未解决问题,如有人能解惑另外开贴给分]
- ¬?请问各位大哥大姐们,在哪里下载SQL Server 2000企业版?
- 请问满足这样条件的Sql语句怎么写?谢谢
- 求救呀 在做报表中,月份的起始点要以上个月的27日开始算起到本月的26日为止,不能以自然月为准;
- 如何判断当前的数据库是sql server7.0还是sql server2000?
- 怎么用视图把数据由正常的已行显示改为已列显示?(在线等待)
- 动态sql语句问题?
- 不理解的错误提示
- 【讨论】生成上班时间9:00到18:00之间的一个随机事件
-_-||今天早上那个确实要用到工作中,这个只是做着玩玩。ssas的mdx语句在返回大量数据环比时,服务有可能会崩溃,所以我只用mdx做聚合,然后用clr算环比。
个人觉得开发语言和sql语句配合还是可以对部分计算的性能有很大的提升空间的。
未来的BI版主把MS要考虑的东西,提前做了...
呵呵.老大那本书上也CLR的章节中提到相关的内容了.
CLR在复杂算法上处理和循环递归等处理上直接操作内存处理还是有优势的.
可以获得性能上的提升。
写完了整个CLR函数系列.
呵呵~