关于SQLServer2005的学习笔记――树形结构问题（sql 树形结构）

25-04-06 5

如果您想了解关于SQLServer2005的学习笔记――树形结构问题和sql树形结构的知识，那么本篇文章将是您的不二之选。我们将深入剖析关于SQLServer2005的学习笔记――树形结构问题的各个方

如果您想了解关于SQLServer2005的学习笔记――树形结构问题和sql 树形结构的知识，那么本篇文章将是您的不二之选。我们将深入剖析关于SQLServer2005的学习笔记――树形结构问题的各个方面，并为您解答sql 树形结构的疑在这篇文章中，我们将为您介绍关于SQLServer2005的学习笔记――树形结构问题的相关知识，同时也会详细的解释sql 树形结构的运用方法，并给出实际的案例分析，希望能帮助到您！

本文目录一览：

关于SQLServer2005的学习笔记――树形结构问题（sql 树形结构）
关于SQLServer2005 的学习笔记（一）——前言
关于SQLServer2005 的学习笔记（二）——体系结构一
关于sqlserver2005的jdbc连接问题
关于SqlServer2005的几个小技巧

关于SQLServer2005的学习笔记――树形结构问题（sql 树形结构）

关于解决树形目录是每种数据库或大多数开发人员都要面对的问题，在这一点上 Oracle 走的跟前线一些，从 9i 起便提供了 connect by 进行了支持， 10g 又增强了相关语法；在 sqlServer2005 中，强大的 CTE 功能也提供了相应的解决方案，此外提供的表函数功能也给出了另外一种解决思路。

从功能上讲的话，表函数方式更为灵活一些，毕竟基于过程的结构方式更容易实现负责的业务逻辑；但递归 CTE 构造起来更为清晰一些。

该文起源于《 Microsoft sql Server 2005 技术内幕： T-sql 查询》，但与文中所述不尽相同。

首先构建一个标准的树形结构的员工表

CREATE TABLE Employees

(

EmpID INT,

MgrID INT,

EmpName VARCHAR(25),

Salary MONEY,

CHECK(EmpID<>MgrID)

);

INSERT INTO Employees VALUES(1,NULL,'David',10000);

INSERT INTO Employees VALUES(2,1,'Eitan',7000);

INSERT INTO Employees VALUES(3,'Ina',7500);

INSERT INTO Employees VALUES(4,2,'Seraph',5000);

INSERT INTO Employees VALUES(5,'Jiru',5500);

INSERT INTO Employees VALUES(6,'Steve',4500);

INSERT INTO Employees VALUES(7,3,'Aaron',5000);

INSERT INTO Employees VALUES(8,5,'Lilach',3500);

INSERT INTO Employees VALUES(9,7,'Rita',3000);

INSERT INTO Employees VALUES(10,'Sean',3000);

INSERT INTO Employees VALUES(11,'Gabriel',3000);

INSERT INTO Employees VALUES(12,9,'Emilia',2000);

INSERT INTO Employees VALUES(13,'Michael',2000);

INSERT INTO Employees VALUES(14,'Didi',1500);

-- 让我们先来看看 Oracle 是如何实现的吧

-- 获取所有相关员工信息，并构建其级别和相应的结构指向

SELECT EmpID,MgrID,EmpName,Salary,Level,sys_connect_by_path(NVL(EmpID,'0'),'->')

FROM Employees

CONNECT BY PRIOR EmpID=MgrID

START WITH MgrID IS NULL

-- 获取员工的所有下级节点

SELECT EmpID,Level

FROM Employees

CONNECT BY PRIOR EmpID=MgrID

START WITH EmpID=9

-- 获取员工的所有上级节点

SELECT EmpID,Level

FROM Employees

CONNECT BY PRIOR MgrID=EmpID

START WITH EmpID=14

-- 构建递归 CTE ，也可以灵活获取满足不同级别的上下级节点

WITH EmployeeTree

(

SELECT EmpID,

0 AS Level,

CAST(CASE WHEN MgrID IS NULL THEN 'Root' END AS VARCHAR(50)) MgrList

FROM Employees

WHERE MgrID IS NULL -- 此处亦可修改为 MgrID=@Root ，即传入的节点，即可得到想要的节点内容

UNION ALL

SELECT C.EmpID,C.MgrID,C.EmpName,C.Salary,

P.Level+1 AS Level,

CAST(CAST(P.MgrList AS VARCHAR(50))+'->'+CAST(C.EmpID AS VARCHAR(10)) AS VARCHAR(50)) MgrList

FROM EmployeeTree P,Employees C

WHERE C.MgrID=P.EmpID --AND P.Level<2 设定相关级别

)

-- 所有员工

SELECT * FROM EmployeeTree

-- 求某员工上级

SELECT * FROM EmployeeTree

WHERE CHARINDEX(MgrList,(SELECT MgrList FROM EmployeeTree WHERE EmpID=7))>0

-- 求某员工下级

SELECT * FROM EmployeeTree

WHERE MgrList LIKE (SELECT MgrList FROM EmployeeTree WHERE EmpID=7)+'%'

-- 求某员工下级并且符合相应级数的

SELECT * FROM EmployeeTree

WHERE MgrList LIKE (SELECT MgrList FROM EmployeeTree WHERE EmpID=7)+'%'

AND Level<=(SELECT Level FROM EmployeeTree WHERE EmpID=7)+1

-- 通过表函数方式返回相关节点

CREATE FUNCTION fn_GetEmployeeTree(@root AS INT)

RETURNS @Subs TABLE

(

EmpID INT,

Level INT

)

BEGIN

DECLARE @Level AS INT;

SET @Level=0;

INSERT INTO @Subs(EmpID,Level) SELECT EmpID,@Level FROM Employees WHERE EmpID=@root;

WHILE @@rowcount>0

BEGIN

SET @Level=@Level+1;

INSERT INTO @Subs(EmpID,Level)

SELECT C.EmpID,@Level

FROM @SubS AS P

JOIN Employees AS C

ON P.Level=@Level-1

AND C.MgrID=P.EmpID

END

RETURN;

END

SELECT * FROM fn_GetEmployeeTree(1)

关于SQLServer2005 的学习笔记（一）——前言

关于sql Server，个人一直认为它是比较简单却又很神秘的数据库；简单之处在于他完全通过图形化界面便可实现所有大型数据库的安装、维护、备份、优化等等；神秘呢在于它所有的一切都隐藏在了图形化界面之下，让人琢磨不透，当需要更深层次的去了解他的时候，却无能为力。

我开始使用sql Server自2002年起，做了一年多基于sql Server数据库的程序开发，然后04、05年又做了两年基于sql Server的数据仓库，因为数据量太大和性能原因所以需要不断进行性能优化和调整。

印象最深刻的是05年在某移动公司实施基于sql Server2000的数据仓库项目。那台数据仓库机器的硬件配置也不高，4G内存，好像是4个cpu，跑的是Windows Server2003；当时每天的数据量为1.2GB，整个数据仓库包括一年的历史数据，总计达到了将近800GB，业务数据源主要来自于一个oracle数据库，当然还有大量的话务文件，话务信息总是以二进制文本存在，并通过解析工具不断的解析到数据库中。

对于sqlServer2000来说，难点主要如下：

1、单表超过1000万记录，查询更新删除性能急转直下

很不幸的是，话单信息每天都要超过3000万了，即使经过处理后的话单也将近1000万；

2、sql Server的锁机制问题，sqlServer2000采用的是已提交读的悲观并发控制，在读和写之间非常容易造成死锁。

3、业务逻辑处理复杂度太高，导致性能问题

一条话务可能会出现几十条话单（最高记录为50条左右，大概是IVR流程设置的问题），最终的话单会被一个长达七八百行的存储过程解析为3条话单，而这个过程基本是连续的；这三条话单再经过ETL处理转变成可统计的话务指标。

4、OLAP也存在严重的性能问题，当时的一个MOLAP就将近100GB了

5、系统稳定性不够，总是莫名其妙的出现作业当掉的情况

针对以上情况，我做了不断的尝试和优化，例如优化表结构设计，把表做成当前表和历史表，历史表再切分成分月表；优化业务逻辑存储过程；增加检验和处理死锁；并针对稳定性问题向微软上海进行咨询和处理，最后下载了一堆的补丁和工具进行反复实践。

说实在的，用sqlServer2000做BI/DW项目，最大的好处是完全通过联机帮助就可以上手，而最大的问题就是稳定性和性能问题，使用sql Server的这段的经验确实让人很痛苦，因为你无从解决。

再后来，转向了Oracle一段时间，同样我对Oracle的整体架构、性能优化和开发稍微了解一些，但无奈的是自己对linux的了解实在匮乏，所以很难专进去。个人认为自个对Oracle的体系架构认识的要比sqlServer更深刻一些。

最近一段时间同步阅读了一下《sqlServe2005技术内幕存储引擎》、《sqlServe2005技术内幕程序设计》、《sqlServe2005技术内幕T-sql查询》，对sqlServer2005有了一些新的认识，总得感觉2005比2000有了质的飞跃，微软也在与Oracle、IBM的不断竞争中成长，而数据库在竞争中不断趋同。

读书第一遍只是为粗略的看一下，不敢去发表什么个人观点；读第二遍的时候把自己熟悉的部分会做下实践和印证；如果还是无法理解，再去读第三遍；这是我对读书的态度。

我会在这段时间陆陆续续把自己对以上三本书的学习心得不断记录下来，并在sqlServer和Oracle之间进行比较，在sqlServer2000和sqlServer2005之间进行比较。

关于SQLServer2005 的学习笔记（二）——体系结构一

sqlServer2005引擎组件

首先让我们先来看看SQL Server2005的引擎组件，sqlServer2005有四大组件：协议、关系引擎、存储引擎和sqlOS。

协议层接收来自于用户的请求（查询、更改、删除、创建表等等操作）并把它们转换成关系引擎能够识别的形式。它还能够获取任意查询、状态信息、错误信息的结果，然后把这些结果转换成客户端能够理解的性，最后再返回客户端。这一层的意义在于是应用程序访问sql Server的接口。

关系引擎层能够接收sql批处理，以及决定如何处理。它能够解析、编译和优化请求并检查批处理的执行过程，对于所需的数据会发送数据请求给存储引擎。这一层的主要是解析、编译和优化，生成语法树，并与存储引擎进行交互。

存储引擎则负责管理所有的数据访问，包括基于事务的命令和大批量操作。这些操作包括备份、批量插入和某些数据库一致性检查。根据图上所示，主要是实现数据的访问、数据的缓存、锁的管理等操作。

sqlOS则可以理解为sql Server2005的操作系统，主要负责处理与操作系统之间的工作，如何进行内存分配、死锁检查、I/O操作等；很难说的清楚sqlOS是sqlServer自己独有的处理机制呢，还是调用OS来完成相关处理的。

sqlServer2000引擎结构

我们再回头看看sqlServer2000的组件关系，在sqlServer2000联机帮助中基本上缺乏相关detail的体系架构图，该图主要也是描述了sqlServer2000中各个主要组件之间的层次和调用关系；而且我对sqlServer2000的体系结构也缺乏深刻认识，再次不做详述。

Oracle体系架构

对于Oracle而言，Oracle并没有提供一个比较全面的组件架构，而是认为Oracle就是数据库和实例的集合，当然我们也可以把SGA各个组成部分、Oracle各种进程、各种数据库文件视为其组件。实例是由SGA和Oracle进程构成的。每一个运行的Oracle数据库都与一个Oracle实例关联。Oracle数据库使用内存结构和进程来管理、访问数据库。所有内存结构都存在于构成数据库服务器的计算机的主存中。进程指的是在这些计算机内存中运行的作业。进程被定义为“控制线程”或操作系统中可以运行一系列步骤的一种机制

实际上从总体结构上看，sqlServer2005也是基于实例和关系数据库的集合，即通过每一个实例来管理若干数据库（Oracle和sql Server的数据库是概念不同的，从Oracle角度而言，sql Server的数据库概念比较怪异，理论上应该与Oracle的逻辑概念表空间是等效的），这些数据库总的来说都是为了满足某个应用需要和业务逻辑而组合起来的一个完整的应用，而每个数据库又是一个相对独立的业务逻辑单位，但他们是为了实现一个共同的目标，因此也不大适合进行分割成若干实例。

sql Server2005的引擎组件更注重的是数据库处理过程中的依次顺序，实际上从实例角度而言，两者可以说是类似的，比如都具备数据缓冲区、字典缓冲区、日志缓冲区、各种数据库进程或线程（数据库读写进程、日志读写进程、检查点）

关于sqlserver2005的jdbc连接问题

今天用sqlserver2005又一次遇到一些问题：

1、驱动问题

正确的形式：Class.forName("com.microsoft.sqlserver.jdbc.sqlServerDriver");

现在用的是2005，而在2000中上述内容应改为Class.forName("com.microsoft.jdbc.sqlserver.sqlServerDriver");

正确形式：url = "jdbc:sqlserver://localhost:1433;databaseName=bankExample";

2000中jdbc:后好像多一个microsoft

2、Tcp连接问题

这个问题是由于sql Server网络配置中的TCP/IP协议没有启用，打开Configuration Manager，启动TCP/IP，然后重启sql Server服务，问题解决

还有一个问题，可能是因为sqlserver没有完整安装的，没有设置向导的缘故，tcp设置以及端口有问题。造成默认端口1433访问不成功，具体方法：

sql Server Configuration Manager—sql2005网络配置—机器协议—TCP/IP（右键属性）—ipall—TCP动态端口（删除）—然后在tcp端口指定端口就行了

关于SqlServer2005的几个小技巧

.五笔的中文输入问题打开表后，如果要在表中直接输入记录，而正好你的机器如果只有五笔输入法的话，好象是不能输入中文的(至少在我的机器上是这样的)，后来得一高人指点，安装了微软拼音，于是就可以输入中文了(MS真是@#$%^&*()!) 2.数据导入导出过程中，追加记录的问题有一段时间，我在使用导出数据时，有时候表结构一模一样，但导出选项中，只有删除目标表，而向目标表中追加记录的选项却不可用，后来发现，不仅表结构要一样，连表名，字段名(区别大小写)都要完全一样，才能选此项(再次向MS说句!@#$%^&*())

关于关于SQLServer2005的学习笔记――树形结构问题和sql 树形结构的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于关于SQLServer2005 的学习笔记（一）——前言、关于SQLServer2005 的学习笔记（二）——体系结构一、关于sqlserver2005的jdbc连接问题、关于SqlServer2005的几个小技巧的相关知识，请在本站寻找。

本文标签：