传智播客－Tree in SQL（译文）（1）

作者简介：
Joe Celko，世界著名的数据库专家，他撰写了多部SQL经典著作，诸如《Analytics and OLAP in SQL》、《Trees and Hierarchies in SQL for Smarties》、《SQL Puzzles and Answers》等等。他也是世界上读者数量最多的SQL书籍作者之一。他曾撰写过一系列专栏，并通过他的新闻组支持了数据库编程技术以及ANSI／ISO标准的发展，曾经担任ANSI SQL标准委员会成员达10年之久。
有位仁兄收集了Joe Celko几本经典著作的PDF版，下面是这位仁兄分享的网址：
http://www.itpub.net/viewthread.php?tid=1233846&extra=&page=1

原文地址：http://intelligent-enterprise.informationweek.com/001020/celko.jhtml

Trees in SQL
SQL树

Some answers to some common questions about SQL trees and hierarchies.
关于SQL树和层级一些常见问题的解答

by Joe Celko

 

This theme is an old one of mine, but it is worth repeating. I have been seeing too many questions about SQL trees and hierarchies in the newsgroup discussions. The usual example of a tree structure in SQL books is called an adjacency list model and it looks like this:
对于我而言这是一个值得老生常谈的话题。我在新闻组讨论中已经见过太多关于SQL树和层级的问题。在SQL书籍中常见的树型结构举例是邻接列表模式，该模式看上去就像这样：
CREATE TABLE Personnel
(emp CHAR(10) NOT NULL PRIMARY KEY,
boss CHAR(10) DEFAULT NULL REFERENCES Personnel(emp),
salary DECIMAL(6,2) NOT NULL DEFAULT 100.00);

 

Another way of representing trees is to show them as nested sets. Because SQL is a set-oriented language, this model is a better than the usual adjacency list approach you see in most textbooks. Let us define a simple Personnel table like this, ignoring the left (lft) and right (rgt) columns for now:
另一种表示树型结构的方式是嵌套集合。因为SQL是一种面向集合的语言，这种模式比你在很多教科书里常见到的邻接列表方式更好。让我们（先）定义一张如下的人事表，现在忽略掉左（lft）和右（rgt）栏位：
CREATE TABLE Personnel
(emp CHAR(10) NOT NULL PRIMARY KEY,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );

 

This problem is always given with a column for the employee and one for his boss in the textbooks. This table (Table 2) -- without the lft and rgt columns -- is called the adjacency list model, after the graph theory technique of the same name; the pairs of nodes are adjacent to each other.
问题在于，在教课书中总是在雇员（employee）表中用一个栏位表示雇员自己，一个栏位表示该雇员的上司。这张表（Table 2）－－没有lft和rgt栏位－－被称为邻接列表模式，以同名的图理论技术命名；一对对节点互相邻近。

 

The organizational chart would look like Figure 1 as a directed graph:
上述组织图表看上去就像Figure 1一样的导向图：