树，图，层次结构在sql中的巧妙使用（一）

一.知识回顾

1. 图

图是一组由边连接起来的项目组成的集合。每一项称为图的一个节点。图的两个顶点之间的连接称为边。
图是对一类数据结构的统称，很多应用场景都可以表示为图。例如：下面要介绍的员工组织图，材料清单图，道路系统等等。
有向/无向 在有向图中，一条边的两个顶点具有某种方向或顺序。无向图中，每条边只是简单连接两个顶点，没有特定顺序。
无环 无环图是指不包含循环的图。意思是，图中没有任何路径开始和结束于同一个顶点。（例如。员工组织图和BOM就是无环图。有向无环图也称为DAG）
2.树
树是一种特殊的图—连通的无环图。
树有一个根节点，访问树时总是从根节点开始。每个节点要么是叶子节点，要么是内部节点。一个内部节点可以有一个或多个子节点，称该节点为这些子节点的父亲。
森林是由一个或多个树组成的集合。
3. 层次结构
一些应用场景可以用层次结构来描述，建模成有向无环图。例如：员工组织图，图的边表示继承关系，代表员工之间的报告关系。这些应用场景中有有向无环的特性。比如，公司中的职责管理链就不能是循环的。
二 . 应用场景
员工组织图以树为模型，只需要一个表：边（经理，员工）和顶点（员工）可以用一行表示。
接下来我们要使用的员工组织图
如图1-1所示

图1-1 员工组织图
运行下图代码，创建Employees表，并且填充数据。

create table dbo.employees
( empid int not null PRIMARY key,
 mgrid int null REFERENCES dbo.employees,  --外键
 empname VARCHAR(25) not null,
 salary money not null,
 check (empid <> mgrid)
 );
 insert into dbo.employees(empid,mgrid,empname,salary) values
 (1,null,'David',$10000.00),
 (2,1,'Eitan',$7000.00),
 (3,1,'Ina',$7500.00),
 (4,2,'Seraph',$5000.00),
 (5,2,'Jiru',$5500.00),
 (6,2,'Steve',$4500.00),
 (7,3,'Aaron',$5000.00),
 (8,5,'Lilach',$3500.00),
 (9,7,'Rita',$3000.00),
 (10,5,'Sean',$3000.00),
 (11,7,'Gabriel',$3000.00),
 (12,9,'Emilia',$2000.00),
 (13,9,'Michael',$2000.00),
 (14,9,'Didi',$1500.00);


 create unique index idx_unc_mgrid_empid 
 on dbo.employees(mgrid,empid);

Employees表的内容

Employees表把管理层次结构表示为一个邻接链表，其中将经理和员工分别表示成父节点和子节点。
材料清单（BOM）
比如一个咖啡店产品的BOM

图 2-2 材料清单图
BOM场景以DAG为模型，因为多个路径可以指向同一个节点，所以这个场景需要两个表；BOM表中的一行表示一条边（组件，零件），而Parts表中的一行表示一个顶点（零件）。
运行如下代码，创建Parts和BOM表，并填充样例数据。

create table dbo.Parts
 (
  partid int not null primary key,
  partname varchar(25) not null
  );

  insert into dbo.Parts(partid,partname) values
  (1,'Black Tea'),
  (2,'white Tea'),
  (3,'Latte'),
  (4,'Espresso'),
  (5,'Double Espresso'),
  (6,'Cup Cover'),
  (7,'Regular Cup'),
  (8,'Stirrer'),
  (9,'Espresso Cup'),
  (10,'Tea Shot'),
  (11,'Milk'),
  (12,'Coffee Shot'),
  (13,'Tea Leaves'),
  (14,'water'),
  (15,'Sugar Bag'),
  (16,'Ground Coffee'),
  (17,'Coffee Beans');


 create table dbo.BOM
  (
    partid int not null REFERENCES dbo.Parts,
	assemblyid int null REFERENCES dbo.Parts,
	unit VARCHAR(3) NOT NULL,
	qty DECIMAL(8,2) not null,
	UNIQUE(partid,assemblyid),
	check(partid <> assemblyid)
	);



	  insert into dbo.BOM