仓酷云

标题: MSSQL编程：SQL Story（十一）－－树状表游戏 [打印本页]

作者: 因胸联盟 时间: 2015-1-16 22:27
标题: MSSQL编程：SQL Story（十一）－－树状表游戏
提供用于管理、检查、优化数据库操作的管理工具。
树状布局的存储与办理，是每个在干系型数据库平台上事情的程序员日夕都要碰到的成绩。说年夜不年夜，怎样都能办理，说小不小，处置欠好，有的是贫苦等着你。仁者见仁，智者见智，公说私有理，婆说婆有理（谁用机箱砸我？机箱是个好器材，乱丢会摔坏硬盘的，你看我话没说完你又把显现器丢了……），咳咳，好吧，闲话少说，我们从最亨衢的处置作风谈一谈吧。这内里的年夜部份内容并不是我的首创，从好久好久之前，数据库程序员们就如许做啦。

树状表的布局化表达

在统统入手下手前，我们先就树状表的暗示体例告竣一个共鸣。在干系型数据库中，我们固然没有举措如许间接暗示一个树：

a

bc

defg

响应的，我们会把它变形为立体表，这类变形让我想起拓扑多少：

rn1n2

abd

abe

acf

acg

存储布局

稍有履历的伴侣，也许都不会试着把树状布局一层一列的存出来了吧。如许做的成绩是不言而喻的：与表中存储的信息布局分歧，表的布局应当是绝对流动的，不克不及任意修改。而关于层数不克不及流动的一般树状布局，这是难以想象的。没有需要会商过量的毛病，我们选择一个绝对准确的体例DD把树状布局笼统成合适干系数据库的情势。

只思索某一个节点的话，和这个节点相干的信息是：它的独一标识、父节点、子节点、数据信息。这个中只要子节点的数量不定。不外假如每个节点都断定了本人的父节点，明显能够省略子节点。如许一来，一个节点必要存储三部分信息DD它的独一标识、父节点、数据信息。一个幻想的TreeView表只必要三个节点就能够了，用SQL语句来表达就是

CREATETABLE[dbo].[TreeView](

[ID][char](10)PRIMARYKEY,

[PID][char](10)FOREIGNKEYREFERENCES[dbo].[TreeView],

[DATA][char](10)

)

ID是以后节点的独一标识号，明显它应该是主键；我们创建的是一个自闭的存储布局，每个节点的父节点也应该出自表中存储的节点，以是PID列援用ID作为外键；至于DATA，它是节点中的信息，一般和树的布局没有相对干系。我把这三列全设成Char(10)，是为了前面做演示更便利，固然也有人喜好用主动标识列来做主键，在这类场所，也自有其长处。为了保护数据的完全性或存储、检索等方面的思索，有用中我们大概会接纳更庞大的布局，不外主干就如许子了。这个布局从数学上讲很简便，并且是自洽的。假如一个节点没有父节点，那末它的PID就即是它本人的ID。这其实不违背我们关于主外键的界说。

信息的办理与利用

树表的布局断定后，成绩就会合在怎样读写个中的数据。

增添节点：增添一个叶节点很复杂，只需指定这个节点的父节点，把它“挂接”到TreeView中。从递回的角度来看，我们能够反复这一步骤，真到拔出一个完全的子树。绝对而言，对照贫苦的是，怎样把一个子节点拔出到现有的树两头，而不是最末了。好比存在一个节点N，它的根为R，如今要在R和N之间拔出一个新节点N’，我们能够如许做：把N’挂在R上面，作为它的子节点，然后把N的父节点指定为N’便可。

修正节点：这里指修正树布局，改动某一节点的父节点，在这类布局中，修正是很便利的，只需挪用尺度的update就能够。

删除节点：删除节点时要注重这个节点上面另有没有子节点，假如有，我们一般以两种体例处置，一是把相干子节点全删失落，假如是MSSQLServer2000如许的体系，你能够很复杂的在建表时将外键束缚指定为撑持级联删除，本人写一个级联删除对照贫苦，不外也不是不成能，重点在于，为这个历程创建递回。扼要示比方下：

--界说存储历程

CREATEPROCEDUREDeleteNode

@NodeIDChar(10)

AS

BEGIN

--以以后节点的子节点作为纪录集创建游标

DECLAREChildNodesCURSOR

READ_ONLY

FORSELECTIDFROMTreeViewWHEREPID=@NodeID

DECLARE@ChildNodeVARCHAR(40)

OPENChildNodes

FETCHNEXTFROMChildNodesINTO@ChildNode

WHILE(@@fetch_status-1)--判别纪录集是不是乐成翻开

BEGIN

IF(@@fetch_status-2)

BEGIN

--递回挪用

EXECDeleteNode@ChildNode

END

FETCHNEXTFROMChildNodesINTO@ChildNode

END

CLOSEChildNodes

DEALLOCATEChildNodes

--代码实行到这里，能够断定@NodeID不再有子节点，如今，我们删除它

DELETEFROMTreeViewWHEREID=@NodeID

END;

固然，这是一种对照低效的计划，每个将要删除的节点都要实行一次Delete，对照无效率的办法是多深切一层，操纵以后节点的子节点。有乐趣的读者能够一试。

查询：树状表的查询是最风趣的内容之一。固然仅仅查出某一个节点的信息没有太年夜的意义，我们但愿的是失掉从以后节点一向向下（一般是到最底层）的完全子树。这一样必要一个递回。让我们从一个归结法游戏入手下手，事前，我们先在TreeView中拔出以下数据：

ID

PID

DATA

----------

----------

----------

ROOT

ROOT

ROOT

N11

ROOT

Node1-1

N12

ROOT

Node1-2

N13

ROOT

Node1-3

N21

N11

Node2-1

N22

N11

Node2-2

N23

N12

Node2-3

N24

N13

Node2-4

N31

N21

Node3-1

N32

N21

Node3-2

N33

N21

Node3-3

N34

N22

Node3-4

归结法第一步，固然是机关初值，查询子树的根节点就是尺度的SQL，SELECTID,DATAFORMTreeViewWHEREID=…，在这里有一个细节，查找表中一切的根节点（仔细的读者大概早就发明，我们计划的这个TreeView能够存储恣意多个树）能够经由过程SELECTR.ID,R.DATAFORMTreeViewRWHERER.ID=R.PID来完成。复杂起见，我们就从这里起步吧。

第二步，选择出前两层也不难，

SELECTR.ID,N1.ID,N1.DATA

FROMTreeViewR

LEFTJOINTreeViewN1

ONR.ID=N1.PID

ANDR.IDN1.ID

WHERER.ID=R.PID

我们要注重的是加蓝的部分，这是增添一层后做修改的部分。

很简单，我们失掉前三层，

SELECTR.ID,N1.ID,N2.ID,N2.DATA

FROMTreeViewR

LEFTJOINTreeViewN1

ONR.ID=N1.PID

ANDR.IDN1.ID

LEFTJOINTreeViewN2

ONN1.ID=N2.PID

ANDN1.IDN2.ID

WHERER.ID=R.PID

一样的，我们重点察看加蓝的部分。信任经由这两步，人人会发明个中的纪律。如今我们能够把这个历程主动化了……

……

不晓得人人是不是还记得前几集里谁人关于四色成绩的笑话，我又一次犯了如许的毛病。一个多月以来，我就陷在这里不克不及自拔。明显，我低估了成绩的难度。这个成绩仿佛不克不及转化为一个复杂表达式，只能经由过程一个递回的流程来完成。而流程化的程序又非SQL所长。这内里有着林林总总的贫苦。信任试过的伴侣都有体味。如今我利用的架构成绩是明显的，它必要明白晓得究竟从子树的极点向下究竟有几层。以是，盘算树的层数就起首被提了出来。

可是，可是，可是……（我几乎没脸见人啦）我一向找不到一个简便文雅的办法。一个多月的工夫就这么已往了。我不能不一点点保持我的准绳（此乃人生出错之始啊）。最初，我得供认，要想写出一个文雅的树状表选择来，对我仍是对照坚苦的。以是，在这一篇文章里，我们先会商到这里，树状表的选择DD实在应当说树状视图机关，仍是留到下次再会商吧。
MySQL这个名字是怎么来的已经不清楚了。基本指南和大量的库和工具带有前缀“my”已经有10年以上，而且不管怎样，MySQLAB创始人之一的的女儿也叫My。

作者: 飘飘悠悠 时间: 2015-1-17 23:41
始终遗憾SQLServer的登陆无法分配CPU/内存占用等指标数。如果你的SQLServer给别人分配了一个只可以读几个表的权限，而这个家伙疯狂的死循环进行连接查询，会给你的系统带来很大的负担。

作者: 小女巫 时间: 2015-1-21 10:59
SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）

作者: 再见西城 时间: 2015-1-30 16:10
再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

作者: 谁可相欹 时间: 2015-2-6 14:00
如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

作者: 兰色精灵 时间: 2015-2-16 09:56
作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

作者: 变相怪杰 时间: 2015-3-5 04:38
分区表是个亮点！从分区表也能看出微软要做大作强SQLServer的信心。资料很多，这里不详细说。但是重点了解的是：现在的SQLServer2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。

作者: 小魔女 时间: 2015-3-11 23:30
一个是把SQL语句写到客户端,可以使用DataSet进行加工;

作者: 海妖 时间: 2015-3-19 16:06
原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

作者: 再现理想 时间: 2015-3-29 01:02
你可以简单地认为适合的就是好，不适合就是不好。

欢迎光临仓酷云 (http://ckuyun.com/)