官方网站：http://www.accessdb.org/

You may have used this before and had it work but now, for some reason, new pages you create won't center vertically. You may be changing pages from standard HTML to XHTML and now tables don't respect the 100% HEIGHT attribute. Read on.

There are a few things you need to know here. There is not now, nor has there ever been a HEIGHT attribute for tables. Scour the HTML specifications and you will not find HEIGHT among the attributes for the table tag. The fact is, table height is invalid code. If you attempt to validate a page where you have assigned a height to a table, it will fail. Assigning a height to the table tag is not the answer. You can, however, legally assign a height using CSS.

Rendering Modes

But why, you ask, did it used to work and now it doesn't? Without going into a lot of specifics, the answer is that it has to do with the browser's rendering mode as determined by the document's DOCTYPE. You can read a lot more about that subject on Eric Meyer's companion site for his book Eric Meyer on CSS. The point is that browsers, rendering pages in quirks mode, expand tables to fill the 100% HEIGHT. Browsers rendering pages in standards compliance mode don't. That means that, if a page has a full and proper DOCTYPE, including the URI, or if it uses an XHTML DOCTYPE, the browser will render the page in standards compliance mode. If a page does not have a DOCTYPE, or if it has a partial DOCTYPE without the URI, the the browser will render the page in quirks mode. Newer web design software has begun inserting the full DOCTYPE in pages, so the pages get rendered in standards compliance mode.

Now don't get ahead of me. About now, you're thinking that all you need to do is remove the DOCTYPE and your tables will render the way you want them to. There are downsides to depending on quirks mode to render your page. Rendering pages in quirks mode, browsers use all the old bugs from prior versions, which means that you are less likely to get consistent rendering across different browsers and/or platforms. You wind up paying a hefty price to obtain your goal.

Fear not! There is a way to accomplish this, without resorting to quirks mode rendering. The answer will not make the invalid code magically become valid, but it will work in most browsers except for Internet Explorer on the Mac platorm.

Why Percentage Height Fails

Okay, to begin, maybe I should explain exactly why 100% height fails. Contrary to popular opinion, it is not because the browser ignores the invalid HEIGHT attribute. The real reason it fails is that the browser does not expand the HTML and/or BODY (depending on the browser) to fill the browser viewport. The browser is in almost all cases, in fact, rendering the table as 100% high. The problem is that it is 100% of the containing element (HTML and BODY), which may not be as high as the browser's veiwport. The HTML and BODY tags represent block elements that automatically expand to fill the width of their container, which is the browser's viewport. They do not expand vertically. That can be fixed. Consider the following CSS:

   html,body{
      margin:0;
      padding:0;
      height:100%;
      border:none
   }
	

What does that do? That CSS tells the browser that the HTML and BODY elements have no padding, margin, or border and that their height is 100% of their containing elements. The containing element is the browser viewport! The margin, padding and border are set to zero because failing to do so would mean that the browser would use its default values and the margin and/or padding would be applied outside the 100% resulting in a scroll bar even when none was really needed.

Using Valid Code

If you're interested in writing valid code, you still have a problem because, in case I failed to mention it, table HEIGHT is invalid. Now you need to decide which is more important to you, writing valid code or getting the 100% height to display in Netscape version 4. Netscape 4's old and buggy implementation of CSS fails to render the CSS specified height.

If you can accept that NN4 will not display the 100% height, the "proper" way to do it would be to set the table height using CSS. My recommendation would be to create a ID selector in your style sheet that could be applied to a particular table. That way, you needn't worry about all tables on the page being affected. The ID definition might look something like this:

#fullheight{height:100%}

You would then apply the fullheight ID to your table like this:

<table id="fullheight" ... >

Vertical Centering

Now that we have achieved 100% height with our table, it becomes a simple matter to center content. Let's take the following CSS:

   html, body, #wrapper {
      height:100%;
      margin: 0;
      padding: 0;
      border: none;
      text-align: center;
   }
   #wrapper {
      margin: 0 auto;
      text-align: left;
      vertical-align: middle;
      width: 400px;
   }
	

Use the above CSS along with the following HTML:

   <body>
   <table id="wrapper">
      <tr>
         <td>This is centered</td>
      </tr>
   </table>
   </body>

The results

Okay, I know that this discussion has been a bit longer and more involved that my usual "examples" and the reason is that I thought it was important to understand how and why the results are what they are. Here are four examples you can look at. Each will open in a new browser window and you can view the source code to see how it's done. The first three examples use exactly the same table markup. The last one uses CSS to style the table and set its height, which is perfectly valid.

• Quirks mode rendering 100% height that works
• Standards mode rendering 100% height that fails
• Standards mode rendering that works with CSS
• Standards mode valid code rendering that works

Like I said, this technique works in almost all browsers. The exception is Internet Explorer on the Mac.

Good luck!

2.达维多定律
　　达维多认为，一个企业要想在市场上总是占据主导地位，那么就要做到第一个开发出新产品，又第一个淘汰自己的老产品。
　　国内网站跟风太严重，比如前段时间的格子网，乞讨网，博客网，一个成功了，大家一拥而上。但实际效果是，第一个出名的往往最成功，所以在网站的定位上，要动自己的脑筋，不是去捡人家剩下的客户。同理，买人家出售的数据来建站效果是很糟糕的。

3.木桶定律
　　水桶定律是指，一只水桶能装多少水，完全取决于它最短的那块木板。这就是说任何一个组织都可能面临的一个共同问题，即构成组织的各个部分往往决定了整个组织的水平。
　　注意审视自己的网站，是速度最糟糕？美工最糟糕？宣传最糟糕？你首先要做的，不是改进你最强的，而应该是你最薄弱的。

4.马太效应
　　《新约》中有这样一个故事，一个国王远行前，交给三个仆人每人一锭银子，吩咐他们：“你们去做生意，等我回来时，再来见我。”国王回来时，第一个仆人说： “主人，你交给我们的一锭银子，我已赚了10锭。”于是国王奖励他10座城邑。第二个仆人报告说：“主人，你给我的一锭银子，我已赚了5锭。” 于是国王例奖励了他5座城邑。第三个仆人报告说：“主人，你给我的一锭银子，我一直包在手巾里存着，我怕丢失，一直没有拿出来。”于是国王命令将第三个仆人的一锭银子也赏给第一个仆人，并且说：“凡是少的，就连他所有的也要夺过来。凡是多的，还要给他，叫他多多益善。”这就是马太效应。
　　在同类网站中，马太效应是很明显的。一个出名的社区，比一个新建的社区，更容易吸引到新客户。启示是，如果你无法把网站做大，那么你要做专。作专之后再做大就更容易。

5.手表定理
　　手表定理是指一个人有一只表时，可以知道现在是几点钟，而当他同时拥有两只表时却无法确定。
一个网站，你只需要关注你特定的用户群需求。不要在意不相干人的看法。

6.不值得定律
　　不值得定律：不值得做的事情，就不值得做好不要过度seo，如果你不是想只做垃圾站。不要把时间浪费在美化再美化页面，优化再优化程序，在你网站能盈利后，这些事情可以交给技术人员完成。

7.彼得原理
　　劳伦斯.彼得认为：在各种组织中，由于习惯于对在某个等级上称职的人员进行晋升提拔，因而雇员总是趋向于晋升到其不称职的地位。
不要轻易改变自己网站的定位。如博客网想变门户，盛大想做娱乐，大家拭目以待吧。

8.零和游戏原理
　　当你看到两位对弈者时，你就可以说他们正在玩“零和游戏”。因为在大多数情况下， 总会有一个赢，一个输，如果我们把获胜计算为得1分，而输棋为-1分，那么，这两人得分之和就是：1+（-1）=0 不要把目光一直盯在你的竞争网站上，不要花太多时间抢它的访客。我们把这些时间用来寻找互补的合作网站，挖掘新访客。

9.华盛顿合作规律
　　华盛顿合作规律说的是： 一个人敷衍了事，两个人互相推诿， 三个人则永无成事之日。
如果你看准一个方向，你自己干，缺人手就招。不要轻易找同伴一起搞网站，否则你会发现，日子似乎越过越快了，事情越做越慢了。

10.邦尼人力定律
　　一个人一分钟可以挖一个洞，六十个人一秒种却挖不了一个洞。合作是一个问题，如何合作也是一个问题。你需要有计划。

11.牛蛙效应
　　把一只牛蛙放在开水锅里，牛蛙会很快跳出来；但当你把它放在冷水里，它不会跳出来，然后慢慢加热，起初牛蛙出于懒惰，不会有什么动作，当水温高到它无法忍受的时候，想出来，但已经没有了力气。
　　如果你是soho，注意关注你的财务。不要等到没钱了再想怎么挣，你会发现那时候挣钱更难。

12.蘑菇管理
　　蘑菇管理是许多组织对待初出茅庐者的一种管理方法，初学者被置于阴暗的角落（不受重视的部门，或打杂跑腿的工作），浇上一头大粪（无端的批评、指责、代人受过），任其自生自灭（得不到必要的指导和提携）。
做网站毕竟要遭遇这样的阶段，搜索引擎不理你，友情链接找不到，访客不上门。这是磨练。

13.奥卡姆剃刀定律
　　如无必要，勿增实体。
把网站做得简单，再简单，简单到非常实用，而不是花俏。google的首页为什么比雅虎好？

14.巴莱多定律(Paredo 也叫二八定律)
　　你所完成的工作里80%的成果，来自于你20%的付出；而80%的付出，只换来20%的成果。
随时衡量你所做的工作，哪些是最有效果的。

15.马蝇效应
　　林肯少年时和他的兄弟在肯塔基老家的一个农场里犁玉米地，林肯吆马，他兄弟扶犁，而那匹马很懒，慢慢腾腾，走走停停。可是有一段时间马走得飞快。 林肯感到奇怪，到了地头，他发现有一只很大的马蝇叮在马身上，他就把马蝇打落了。看到马蝇被打落了，他兄弟就抱怨说：”哎呀，你为什么要打掉它，正是那家伙使马跑起来的嘛！”
　　在你心满意足的时候，去寻找你的马蝇。没有firefox，不会有ie7，firefox就是微软的马蝇之一。马蝇不可怕，怕的是会一口吃掉你的东西，像 ie当初对网景干的那样。

16.最高气温效应
　　每天最热总是下午2 时左右，我们总认为这个时候太阳最厉害，其实这时的太阳早已偏西，不再是供给最大热量的时候了。此时气温之所以最高，不过是源于此前的热量积累。
你今天的网站流量，是你一个星期或更长时间前所做的事带来的。

17.超限效应(溢出效应)
　　刺激过多、过强和作用时间过久而引起心理极不耐烦或反抗的心理现象，称之为“超限效应”。 别到别人论坛里发太多广告。别在自己网站上放太多广告。别在自己的论坛里太多地太明显地诱导话题。

18.懒蚂蚁效应
　　生物学家研究发现，成群的蚂蚁中，大部分蚂蚁很勤劳，寻找、搬运食物争先恐后，少数蚂蚁却东张西望不干活。当食物来源断绝或蚁窝被破坏时，那些勤快的蚂蚁一筹莫展。“懒蚂蚁”则“挺身而出”，带领众伙伴向它早已侦察到的新的食物源转移。
　　不要把注意力仅仅放在一个网站上，即使这个网站现在为你带来一切。你要给自己一些时间寻找新的可行的方向，以备万一。

19.长尾理论
　　ChrisAnderson认为，只要存储和流通的渠道足够大，需求不旺或销量不佳的产品共同占据的市场份额就可以和那些数量不多的热卖品所占据的市场份额相匹敌甚至更大。对于搜索引擎，未必你需要一个热门词排在第一位，如果有一千个冷门词排在第一位，效果不但一样，还会更稳定更长远。

20.破窗理论
　　栋建筑上的一块玻璃，又没有及时修好，别人就可能受到某些暗示性的纵容，去打碎更多的玻璃。
　　管理论坛时，如果你发现第一个垃圾贴，赶紧删掉他吧。想想：落伍现在为什么那么多××贴？现在控制比最初控制难多了。

21.“羊群效应”，又称复制原则(Copy Strategy)
　　一个羊群(集体)是一个很散乱的组织，平时大家在一起盲目地左冲右撞。如果一头羊发现了一片肥沃的绿草地，并在那里吃到了新鲜的青草，后来的羊群就会一哄而上，争抢那里的青草，全然不顾旁边虎视眈眈的狼，或者看不到其它地方还有更好的青草。
不要轻易跟风，保持自己思考的能力。

22.墨菲定律
　　如果坏事情有可能发生，不管这种可能性多么小，它总会发生，并引起最大可能的损失。
除非垃圾站，否则不要作弊，对搜索引擎不要，对广告也不要。

23.光环效应
　　人们对人的某种品质或特点有清晰的知觉，印象比较深刻、突出， 这种强烈的知觉， 就像月晕形式的光环一样，向周围弥漫、扩散，掩盖了对这个人的其他品质或特点的认识。
不要轻易崇拜一个人或者公司、一个概念、一种做法。

24.蝴蝶效应
　　一只亚马逊河流域热带雨林中的蝴蝶，偶尔扇动几下翅膀，两周后，可能在美国德克萨斯州引起一场龙卷风。
不管你做什么，网站或者其他，你都应该关注新闻。机遇或者灾难可能就在那。

25.阿尔巴德定理
　　一个企业经营成功与否，全靠对顾客的要求了解到什么程度。
　　我赞同别人的点评：看到了别人的需要，你就成功了一半；满足了别人的需求，你就成功了全部。尤其是做网站。

26.史密斯原则
　　如果你不能战胜他们，你就加入到他们之中去。
　　不要试图做孤胆英雄。如果潮流挡不住，至少，你要去思考为什么。

1.WinRAR 3.8 正式评估版本，进行安装（为了易懂，与中文版为例）

2.upx (一款先进的可执行程序文件压缩器)

一、把upx、原版文件wrar380sc.exe和注册文件rarreg.key准备好，放在某个目录下（例如E：\）如图：

二、用upx提取两个文件——自解压模块setup.sfx和安装脚本setup.s

在命令提示中将E:\作为当前目录，在提示符后面输入命令：

upx\upx.exe e:\wrar380sc.exe -oe:\setup.sfx --overlay=strip

wrar380sc\rar.exe cw e:\wrar380sc.exe e:\setup.s

三、将自解压模块setup.sfx拷贝到你的WinRAR安装目录

四、选中wrar361sc文件夹中的所有文件，右键/添加到压缩文件，并按图进行设置

五、在注释一项中浏览找到E:\setup.s，这个文件可以用记事本打开自己进行个性化设置，呵呵……

六、最后选择高级/自解压选项/模块，选择setup.sfx，完成压缩，一个纯静版免注册的WinRAR就制作好了，哈哈……

Project Home | Downloads | Subversion

Features:

15 inventive and puzzling levels
Unique drawing control in a truly dynamic physics environment 3 beautiful worlds inspired by childrens drawings
Death traps, monsters, and challenging puzzles Unlockable challenges, secrets and rewards
Original and awesome soundtrack by Analogik

Release Name: Max.and.the.Magic.Marker.RIP-Unleashed
Size: 72.6 MB

Download:
 
http://hotfile.com/dl/27346440/9595a86/MaMMarker-Unleashed.zip.html
or
http://ugotfile.com/file/817972/MaMMarker-Unleashed.zip
or
http://rapidshare.com/files/346517951/MaMMarker-Unleashed.zip

下载：Joe Celko's.Trees and Hierarchies in SQL for Smarties.pdf

其它资源：http://www.itpub.net/thread-1233846-1-1.html

Introduction

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.

For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. For our purposes we will use the following product category hierarchy from an fictional electronics store:

These categories form a hierarchy in much the same way as the other examples cited above. In this article we will examine two models for dealing with hierarchical data in MySQL, starting with the traditional adjacency list model.

The Adjacency List Model

Typically the example categories shown above will be stored in a table like the following (I'm including full Create and Insert statements so you can follow along):

Create TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);
Insert INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);
Select * FROM category orDER BY category_id;
+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this case electronics, has a NULL value for its parent. The adjacency list model has the advantage of being quite simple, it is easy to see that FLASH is a child of mp3 players, which is a child of portable electronics, which is a child of electronics. While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can be more problematic in pure SQL.

Retrieving a Full Tree

The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:

Select t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
Where t1.name = 'ELECTRONICS';
+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

Finding all the Leaf Nodes

We can find all the leaf nodes in our tree (those with no children) by using a LEFT JOIN query:

Select t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
Where t2.category_id IS NULL;
+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

Retrieving a Single Path

The self-join also allows us to see the full path through our hierarchies:

Select t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
Where t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';
+-------------+----------------------+-------------+-------+
| lev1        | lev2                 | lev3        | lev4  |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)

The main limitation of such an approach is that you need one self-join for every level in the hierarchy, and performance will naturally degrade with each level added as the joining grows in complexity.

Limitations of the Adjacency List Model

Working with the adjacency list model in pure SQL can be difficult at best. Before being able to see the full path of a category we have to know the level at which it resides. In addition, special care must be taken when deleting nodes because of the potential for orphaning an entire sub-tree in the process (delete the portable electronics category and all of its children are orphaned). Some of these limitations can be addressed through the use of client-side code or stored procedures. With a procedural language we can start at the bottom of the tree and iterate upwards to return the full tree or a single path. We can also use procedural programming to delete nodes without orphaning entire sub-trees by promoting one child element and re-ordering the remaining children to point to the new parent.

The Nested Set Model

What I would like to focus on in this article is a different approach, commonly referred to as the Nested Set Model. In the Nested Set Model, we can look at our hierarchy in a new way, not as nodes and lines, but as nested containers. Try picturing our electronics categories this way:

Notice how our hierarchy is still maintained, as parent categories envelop their children.We represent this form of hierarchy in a table through the use of left and right values to represent the nesting of our nodes:

Create TABLE nested_category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);
Insert INTO nested_category
VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),
(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);
Select * FROM nested_category orDER BY category_id;
+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

We use lft and rgt because left and right are reserved words in MySQL, see http://dev.mysql.com/doc/mysql/en/reserved-words.html for the full list of reserved words.

So how do we determine left and right values? We start numbering at the leftmost side of the outer node and continue to the right:

This design can be applied to a typical tree as well:

When working with a tree, we work from left to right, one layer at a time, descending to each node's children before assigning a right-hand number and moving on to the right. This approach is called the modified preorder tree traversal algorithm.

Retrieving a Full Tree

We can retrieve the full tree through the use of a self-join that links parents with nodes on the basis that a node's lft value will always appear between its parent's lft and rgt values:

Select node.name
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'ELECTRONICS'
orDER BY node.lft;
+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| TELEVISIONS          |
| TUBE                 |
| LCD                  |
| PLASMA               |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
| CD PLAYERS           |
| 2 WAY RADIOS         |
+----------------------+

Unlike our previous examples with the adjacency list model, this query will work regardless of the depth of the tree. We do not concern ourselves with the rgt value of the node in our BETWEEN clause because the rgt value will always fall within the same parent as the lft values.

Finding all the Leaf Nodes

Finding all leaf nodes in the nested set model even simpler than the LEFT JOIN method used in the adjacency list model. If you look at the nested_category table, you may notice that the lft and rgt values for leaf nodes are consecutive numbers. To find the leaf nodes, we look for nodes where rgt = lft + 1:

Select name
FROM nested_category
Where rgt = lft + 1;
+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

Retrieving a Single Path

With the nested set model, we can retrieve a single path without having multiple self-joins:

Select parent.name
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
orDER BY parent.lft;
+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
+----------------------+

Finding the Depth of the Nodes

We have already looked at how to show the entire tree, but what if we want to also show the depth of each node in the tree, to better identify how each node fits in the hierarchy? This can be done by adding a COUNT function and a GROUP BY clause to our existing query for showing the entire tree:

Select node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
orDER BY node.lft;
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| ELECTRONICS          |     0 |
| TELEVISIONS          |     1 |
| TUBE                 |     2 |
| LCD                  |     2 |
| PLASMA               |     2 |
| PORTABLE ELECTRONICS |     1 |
| MP3 PLAYERS          |     2 |
| FLASH                |     3 |
| CD PLAYERS           |     2 |
| 2 WAY RADIOS         |     2 |
+----------------------+-------+

We can use the depth value to indent our category names with the CONCAT and REPEAT string functions:

Select CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
orDER BY node.lft;
+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

Of course, in a client-side application you will be more likely to use the depth value directly to display your hierarchy. Web developers could loop through the tree, adding <li></li> and <ul></ul> tags as the depth number increases and decreases.

Depth of a Sub-Tree

When we need depth information for a sub-tree, we cannot limit either the node or parent tables in our self-join because it will corrupt our results. Instead, we add a third self-join, along with a sub-query to determine the depth that will be the new starting point for our sub-tree:

Select node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
Select node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
orDER BY node.lft
)AS sub_tree
Where node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
orDER BY node.lft;
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| FLASH                |     2 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

This function can be used with any node name, including the root node. The depth values are always relative to the named node.

Find the Immediate Subordinates of a Node

Imagine you are showing a category of electronics products on a retailer web site. When a user clicks on a category, you would want to show the products of that category, as well as list its immediate sub-categories, but not the entire tree of categories beneath it. For this, we need to show the node and its immediate sub-nodes, but no further down the tree. For example, when showing the PORTABLE ELECTRONICS category, we will want to show MP3 PLAYERS, CD PLAYERS, and 2 WAY RADIOS, but not FLASH.

This can be easily accomplished by adding a HAVING clause to our previous query:

Select node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
Select node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
orDER BY node.lft
)AS sub_tree
Where node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
orDER BY node.lft;
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

If you do not wish to show the parent node, change the HAVING depth <= 1 line to HAVING depth = 1.

Aggregate Functions in a Nested Set

Let's add a table of products that we can use to demonstrate aggregate functions with:

Create TABLE product(
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40),
category_id INT NOT NULL
);
Insert INTO product(name, category_id) VALUES('20" TV',3),('36" TV',3),
('Super-LCD 42"',4),('Ultra-Plasma 62"',5),('Value Plasma 38"',5),
('Power-MP3 5gb',7),('Super-Player 1gb',8),('Porta CD',9),('CD To go!',9),
('Family Talk 360',10);
Select * FROM product;
+------------+-------------------+-------------+
| product_id | name              | category_id |
+------------+-------------------+-------------+
|          1 | 20" TV            |           3 |
|          2 | 36" TV            |           3 |
|          3 | Super-LCD 42"     |           4 |
|          4 | Ultra-Plasma 62"  |           5 |
|          5 | Value Plasma 38"  |           5 |
|          6 | Power-MP3 128mb   |           7 |
|          7 | Super-Shuffle 1gb |           8 |
|          8 | Porta CD          |           9 |
|          9 | CD To go!         |           9 |
|         10 | Family Talk 360   |          10 |
+------------+-------------------+-------------+

Now let's produce a query that can retrieve our category tree, along with a product count for each category:

Select parent.name, COUNT(product.name)
FROM nested_category AS node ,
nested_category AS parent,
product
Where node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_id = product.category_id
GROUP BY parent.name
orDER BY node.lft;
+----------------------+---------------------+
| name                 | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS          |                  10 |
| TELEVISIONS          |                   5 |
| TUBE                 |                   2 |
| LCD                  |                   1 |
| PLASMA               |                   2 |
| PORTABLE ELECTRONICS |                   5 |
| MP3 PLAYERS          |                   2 |
| FLASH                |                   1 |
| CD PLAYERS           |                   2 |
| 2 WAY RADIOS         |                   1 |
+----------------------+---------------------+

This is our typical whole tree query with a COUNT and GROUP BY added, along with a reference to the product table and a join between the node and product table in the Where clause. As you can see, there is a count for each category and the count of subcategories is reflected in the parent categories.

Adding New Nodes

Now that we have learned how to query our tree, we should take a look at how to update our tree by adding a new node. Let's look at our nested set diagram again:

If we wanted to add a new node between the TELEVISIONS and PORTABLE ELECTRONICS nodes, the new node would have lft and rgt values of 10 and 11, and all nodes to its right would have their lft and rgt values increased by two. We would then add the new node with the appropriate lft and rgt values. While this can be done with a stored procedure in MySQL 5, I will assume for the moment that most readers are using 4.1, as it is the latest stable version, and I will isolate my queries with a LOCK TABLES statement instead:

LOCK TABLE nested_category WRITE;
Select @myRight := rgt FROM nested_category
Where name = 'TELEVISIONS';
Update nested_category SET rgt = rgt + 2 Where rgt > @myRight;
Update nested_category SET lft = lft + 2 Where lft > @myRight;
Insert INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
UNLOCK TABLES;
We can then check our nesting with our indented tree query:
Select CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
orDER BY node.lft;
+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

If we instead want to add a node as a child of a node that has no existing children, we need to modify our procedure slightly. Let's add a new FRS node below the 2 WAY RADIOS node:

LOCK TABLE nested_category WRITE;
Select @myLeft := lft FROM nested_category
Where name = '2 WAY RADIOS';
Update nested_category SET rgt = rgt + 2 Where rgt > @myLeft;
Update nested_category SET lft = lft + 2 Where lft > @myLeft;
Insert INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;

In this example we expand everything to the right of the left-hand number of our proud new parent node, then place the node to the right of the left-hand value. As you can see, our new node is now properly nested:

Select CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
orDER BY node.lft;
+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

Deleting Nodes

The last basic task involved in working with nested sets is the removal of nodes. The course of action you take when deleting a node depends on the node's position in the hierarchy; deleting leaf nodes is easier than deleting nodes with children because we have to handle the orphaned nodes.

When deleting a leaf node, the process if just the opposite of adding a new node, we delete the node and its width from every node to its right:

LOCK TABLE nested_category WRITE;
Select @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
Where name = 'GAME CONSOLES';
Delete FROM nested_category Where lft BETWEEN @myLeft AND @myRight;
Update nested_category SET rgt = rgt - @myWidth Where rgt > @myRight;
Update nested_category SET lft = lft - @myWidth Where lft > @myRight;
UNLOCK TABLES;

And once again, we execute our indented tree query to confirm that our node has been deleted without corrupting the hierarchy:

Select CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
orDER BY node.lft;
+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

This approach works equally well to delete a node and all its children:

LOCK TABLE nested_category WRITE;
Select @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
Where name = 'MP3 PLAYERS';
Delete FROM nested_category Where lft BETWEEN @myLeft AND @myRight;
Update nested_category SET rgt = rgt - @myWidth Where rgt > @myRight;
Update nested_category SET lft = lft - @myWidth Where lft > @myRight;
UNLOCK TABLES;

And once again, we query to see that we have successfully deleted an entire sub-tree:

Select CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
orDER BY node.lft;
+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

The other scenario we have to deal with is the deletion of a parent node but not the children. In some cases you may wish to just change the name to a placeholder until a replacement is presented, such as when a supervisor is fired. In other cases, the child nodes should all be moved up to the level of the deleted parent:

LOCK TABLE nested_category WRITE;
Select @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
Where name = 'PORTABLE ELECTRONICS';
Delete FROM nested_category Where lft = @myLeft;
Update nested_category SET rgt = rgt - 1, lft = lft - 1 Where lft BETWEEN @myLeft AND @myRight;
Update nested_category SET rgt = rgt - 2 Where rgt > @myRight;
Update nested_category SET lft = lft - 2 Where lft > @myRight;
UNLOCK TABLES;

In this case we subtract two from all elements to the right of the node (since without children it would have a width of two), and one from the nodes that are its children (to close the gap created by the loss of the parent's left value). Once again, we can confirm our elements have been promoted:

Select CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
Where node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
orDER BY node.lft;
+---------------+
| name          |
+---------------+
| ELECTRONICS   |
|  TELEVISIONS  |
|   TUBE        |
|   LCD         |
|   PLASMA      |
|  CD PLAYERS   |
|  2 WAY RADIOS |
|   FRS         |
+---------------+

Other scenarios when deleting nodes would include promoting one of the children to the parent position and moving the child nodes under a sibling of the parent node, but for the sake of space these scenarios will not be covered in this article.

Final Thoughts

While I hope the information within this article will be of use to you, the concept of nested sets in SQL has been around for over a decade, and there is a lot of additional information available in books and on the Internet. In my opinion the most comprehensive source of information on managing hierarchical information is a book called Joe Celko's Trees and Hierarchies in SQL for Smarties, written by a very respected author in the field of advanced SQL, Joe Celko. Joe Celko is often credited with the nested sets model and is by far the most prolific author on the subject. I have found Celko's book to be an invaluable resource in my own studies and highly recommend it. The book covers advanced topics which I have not covered in this article, and provides additional methods for managing hierarchical data in addition to the Adjacency List and Nested Set models.

In the References / Resources section that follows I have listed some web resources that may be of use in your research of managing hierarchal data, including a pair of PHP related resources that include pre-built PHP libraries for handling nested sets in MySQL. Those of you who currently use the adjacency list model and would like to experiment with the nested set model will find sample code for converting between the two in the Storing Hierarchical Data in a Database resource listed below.

1. 为优化MSSQL加了N多垃圾代码，提高百万数据量时的速度。
2. 添加CreateIndex()方法，可以在DEBUG模式下帮助数据库新手创建简单索引。
3. 添加GetRows()方法。
4. 临时删除Oracle相关代码。
5. 生成分页列表时不再采用字符串串联，改用数组。
6. 其他小修改。

建一个ASP函数:
http://jorkin.reallydo.com/article.asp?id=653

ASP代码:
<%
sNewOrder = NewID("id") '这里的ID是数据类型为数字(最好用自动编号列)
Set ors = Exec("Select Top 20 id,compname,fund,foundyear," & sNewOrder & " AS NewID From company where fund>100 order By " & sNewOrder)
Trace(oRs)
%>

说明：这个方法如果在记录集过大的时候也会很费时间，所以请先通过Where条件筛选后再随机排序。

实例下载