传智播客－Tree in SQL（译文）（3）
This has some predictable results that we can use for building queries. The root is always of the form (left = 1, right = 2 * (SELECT COUNT(*) from TreeTable)); leaf nodes always have (left + 1 = right); the BETWEEN predicate defines the subtrees; and so on. Here are some common queries that you can use to build others:
我们可以利用一些可预见的结果构建查询（表达式）。根部总是以这种形式构成（left = 1, right = 2 * (SELECT COUNT(*) from TreeTable)）；叶子节点则总是（left + 1 = right）；BETWEEN表达定义了子树；等等。这里有一些常用的查询，你可以用它们来组建更多其他（查询）：
1. Find an employee and all his/her supervisors, no matter how deep the tree.
查找一个雇员及他/她的所有上级，无论这颗树结构有多深。
SELECT P2.*
from Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp = :myemployee;
2. Find the employee and all his/her subordinates. (This query has a nice symmetry with the first query.)
查找雇员及他/她所有下属。（这个查询与第一个查询形成良好的对称。）
SELECT P2.*
from Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P2.emp = :myemployee;
3. Add a GROUP BY and aggregate functions to these basic queries and you have hierarchical reports. For example, the total salaries that each employee controls:
给这些基本查询添加GROUP BY（关键字）和总计函数这样你就有分级记录了。例如，每个雇员能支配的薪酬总数：
SELECT P2.emp, SUM(S1.salary)
from Personnel AS P1, Personnel AS P2,
Salaries AS S1
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp = S1.emp
GROUP BY P2.emp;
In the adjacency list method, this has to be done with a cursor.
在邻接列表模式里，必须依靠游标（cursor）才能这样做。
4. Find the level of each node, so you can print the tree as an indented listing.
查找每个节点的级别，这样你就能够以缩进列表形式打印这棵树了。
SELECT COUNT(P2.emp) AS indentation, P1.emp
from Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
GROUP BY P1.emp
ORDER BY P1.lft;
5. The nested set mod
相关文档：
--第一步
--在master库中建立一个备份数据库的存储过程.
USE master
GO
CREATE PROC p
@db_name sysname, --数据库名
@bk_path NVARCHAR(1024) --备份文件的路径
A ......
SQL:
using System.Data.SqlClient;
string sql = "server=.;uid=sa;pwd=;database=tablename;";
ACCESS:
using System.Data.OleDb;
string sql = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" + HttpRuntime.AppDomainAppPath + "//App_Data//db.mdb";
HttpRuntime.AppDomainAppPath 为根目录
......
一，
bcp命令详解
bcp命令是SQL Server提供的一个快捷的数据导入导出工具。使用它不需要启动任何图形管理工具就能以高效的方式导入导出数据。bcp是SQL Server中负责导入导出数据的一个命令行工具，它是基于DB-Library的，并且能以并行的方式高效地导入导出大批量的数据。bcp可以将数据库的表或视图直接导出 ......
从昨天开始就一直在网上查找数据库为SQL server 2000 的JSP开发环境的搭建，到现在为止终于能够正常联接并访问。以下是搭建的步骤：
1、从网上下载一个SQL server 2000 个人版的安装包，按照提示操作，当在身分验证时，选择混合模式，用户sa的密码可以为空，也可以不为空。
2、从网上下载一个SQ ......
实现小数据量和海量数据的通用分页显示存储过程
建立一个 Web 应用，分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法，也就是利用ADO自带的分页功能（利用游标）来实现分页。但这种分页方法仅适用于较小数据量的情形，因为游标本身有缺点：游标是存放在内存中，� ......
