理解SQL Server中的父子维度

日期： 2010-02-04 作者：Baya Pavliashvili翻译：高奎 来源：TechTarget中国 英文

SQL Server 2005 中的父子维，解决了如何处理在一个表当中一些记录与本表中其它记录相关联的这样一个常见业务问题。例如：每个员工都有一个直接主管，他的直接主管也有自己的上司，这样一路指向企业的所有者或CEO。 　　同样的，如果您的企业为具有组织机构格局的客户服务，那么每一个个体都可能是另一个大机构或部门的子机构。最小的客户团体可能是一个子部门，然后一直向上到其对应的上级部门，再到几个部门的集合体，再到一个大的市场，区域，公司最后直到整个集团。

　　您的报表和分析结果经常需要把这些相关联的记录进行整合分组。举一个较为常见的例子：一般查看一个销售经理的销售情况，也都连带着他下面所有的销售……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

SQL Server 2005 中的父子维，解决了如何处理在一个表当中一些记录与本表中其它记录相关联的这样一个常见业务问题。例如：每个员工都有一个直接主管，他的直接主管也有自己的上司，这样一路指向企业的所有者或CEO。

　　同样的，如果您的企业为具有组织机构格局的客户服务，那么每一个个体都可能是另一个大机构或部门的子机构。最小的客户团体可能是一个子部门，然后一直向上到其对应的上级部门，再到几个部门的集合体，再到一个大的市场，区域，公司最后直到整个集团。

　　您的报表和分析结果经常需要把这些相关联的记录进行整合分组。举一个较为常见的例子：一般查看一个销售经理的销售情况，也都连带着他下面所有的销售人员的销售情况一起查看了。

　　因为每条记录对应的父记录的数量不同，因此在SQL语句中处理这种递归关系比较困难;很多时候人们会把这种情况作为一种不平衡或不规则的层次结构来看待。例如，让我们回顾一个非常小的"employees"表格：

　　CREATE TABLE [dbo].[Employees](
　　[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
　　[Name] [nvarchar](20) NOT NULL,
　　[Title] [nvarchar](30) NULL,
　　[ReportsTo] [int] NULL
　　CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
　　(
　　[EmployeeID] ASC
　　)
　　)
　　ALTER TABLE [dbo].[Employees] ADD
　　CONSTRAINT [FK_Employees_Employees1] FOREIGN KEY([ReportsTo])
　　REFERENCES [dbo].[Employees] ([EmployeeID])

　　比方说我们在表中只有四条记录，如下表：

　　在这个假设的组织机构里面，Andrew 是这里的“一把手”，因为他没有直接主管，所以他没有对应的父级记录;Janet 的记录对应有一个父级记录，Margaret的记录对应有两个父级记录，Steven的记录对应有三个父级记录。

　　怎样才能够从这张员工表里面得到一个组织关系的图表?比如我们想要查询Steven和他的直接上级的记录。这只是一个简单的关系，语句如下：

　　SELECT
　　a.EmployeeID,
　　a.Name,
　　a.Title,
　　b.Name AS SupervisorName,
　　a.ReportsTo
　　FROM Employees a
　　LEFT JOIN Employees b ON a.ReportsTo = b.EmployeeID
　　WHERE a.EmployeeID = 5

　　但是，这里我们仅仅看到了Steven 和Margaret。要想在这个层次结构里面获得下一个人员的记录，就不得不对Employees表进行两次自身关联。如果想要得到完整的层级记录那就得对Employees表进行三次自身关联;

　　语句如下：

　　SELECT a.EmployeeID,
　　a.Name,
　　a.Title,
　　b.Name AS SupervisorName,
　　c.Name AS NextSupervisorName,
　　d.Name AS TopSupervisorName
　　FROM Employees a
　　LEFT JOIN Employees b ON a.ReportsTo = b.EmployeeID
　　LEFT JOIN Employees c ON b.ReportsTo = c.EmployeeID
　　LEFT JOIN Employees d ON c.ReportsTo = d.EmployeeID
　　WHERE a.EmployeeID = 5

　　查询结果：

　　上面的语句在小型公司里面是可以正常工作的，但是很明显，要想使用SQL语句快速方便的在一个具有递归关系的组织机构里面对成员进行查找是很困难的。如果我们要想查询Steven的销售情况，连同他所属的每一个上司的销售情况，那么查询就会变得越来越笨重。对于每一个管理者，我们的查询都要包括他自己的销售情况，和他所有下属的销售情况。例如：

　　SELECT
　　(SELECT COUNT(*) FROM employees a1
　　INNER JOIN orders b ON a1.employeeid = b.employeeid
　　WHERE a1.employeeid = a.employeeid) AS [steven's sales],
　　(SELECT COUNT(*) FROM employees a1
　　INNER JOIN orders b ON a1.employeeid = b.employeeid
　　WHERE a1.employeeid = a2.employeeid
　　OR a1.employeeid = a.employeeid) AS [margaret's sales],
　　(SELECT COUNT(*) FROM employees a1
　　INNER JOIN orders b ON a1.employeeid = b.employeeid
　　WHERE a1.employeeid = a3.employeeid
　　OR a1.employeeid = a.employeeid
　　OR a1.employeeid = a2.employeeid
　　OR a1.employeeid = a3.employeeid) AS [janet's sales],
　　(SELECT COUNT(*) FROM employees a1
　　INNER JOIN orders b ON a1.employeeid = b.employeeid
　　WHERE a1.employeeid = a4.employeeid
　　OR a1.employeeid = a.employeeid
　　OR a1.employeeid = a2.employeeid
　　OR a1.employeeid = a3.employeeid
　　OR a1.employeeid = a4.employeeid) AS [andrew's sales]
　　FROM employees a INNER JOIN employees a2
　　ON a.reportsto = a2.employeeid
　　INNER JOIN employees a3 ON a2.reportsto=a3.employeeid
　　INNER JOIN employees a4 ON a3.reportsto=a4.employeeid
　　WHERE a.employeeid = 5
　　查询结果：

　　(译者注：在SQL SERVER 2005及其以上版本中已经支持了递归查询，有兴趣的读者可以参考“with as()”语法。)

作者

Baya Pavliashvili
Baya Pavliashvili

相关推荐