SQL图示树形数据表查询存储过程
1、引言
所谓树形层次数据表 ,通常指的是通过两个父子ID字段来标识记录行之间的层次关系,即在关系数据中实现树形数据结构。如SQL SERVER数据库Northwind中表Employees, 用EmployeeID字段引用ReportsTo字段表示领导与被领导关系 。
对于树形层次数据表, 前端程序可以通过TREEVIEW控件来观察, 而在后端查询分析器中,只能看到层次关系不明显的数据行记录,为了在数据库设计或维护时便于观察树形层次数据表,有必要设计一个较通用的存储过程或函数来解决这一典型问题。
基于以上基本需求,笔者设计的脚本程序满足以下几个基本点:
- 出于使用方便和部署方便的要求, 尽量用一个过程和函数来完成;
- 父子表示字段可以是数值型也可以是字符性,扩大适用能力;
- 原始表记录行的顺序是杂乱的,不一定先父后子,而查询结果显示顺序必须先父后子;
- 力争SQL2000就能完成
2、实现源码
--
---------------------------------------------------------------------------------------------------------------------------------------
-- 显示树型结构数据集
-- CREATE: 项前,2008-4-14
-- EXAMPLE:
-- DECLARE @TABLE NVARCHAR(50),@PIDCOL NVARCHAR(50),@IDCOL NVARCHAR(50),@DESCCOL NVARCHAR(50)
-- SELECT @TABLE ='Northwind..Employees',@PIDCOL ='ReportsTo',@IDCOL ='EmployeeID',@DESCCOL ='LastName'
-- EXEC USP_TREEVIEW @TABLE ,@PIDCOL ,@IDCOL ,@DESCCOL
-- ---------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE USP_TREEVIEW
@TABLE NVARCHAR ( 50 ) -- 表名
, @PIDCOL NVARCHAR ( 50 ) -- 父结点标识字段名
, @IDCOL NVARCHAR ( 50 ) -- 子结点标识字段名
, @DESCCOL NVARCHAR ( 50 ) -- 显示节点标签字段
AS
BEGIN
DECLARE @SQL VARCHAR ( 8000 )
SELECT @SQL = '
DECLARE @TMP TABLE(
[PID_NUM] [INT] NULL,
[ID_NUM] [int] IDENTITY (1, 1) NOT NULL ,
[ ' + @PIDCOL + ' ] [nvarchar] (20) NULL ,
[ ' + @IDCOL + '
-- 显示树型结构数据集
-- CREATE: 项前,2008-4-14
-- EXAMPLE:
-- DECLARE @TABLE NVARCHAR(50),@PIDCOL NVARCHAR(50),@IDCOL NVARCHAR(50),@DESCCOL NVARCHAR(50)
-- SELECT @TABLE ='Northwind..Employees',@PIDCOL ='ReportsTo',@IDCOL ='EmployeeID',@DESCCOL ='LastName'
-- EXEC USP_TREEVIEW @TABLE ,@PIDCOL ,@IDCOL ,@DESCCOL
-- ---------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE USP_TREEVIEW
@TABLE NVARCHAR ( 50 ) -- 表名
, @PIDCOL NVARCHAR ( 50 ) -- 父结点标识字段名
, @IDCOL NVARCHAR ( 50 ) -- 子结点标识字段名
, @DESCCOL NVARCHAR ( 50 ) -- 显示节点标签字段
AS
BEGIN
DECLARE @SQL VARCHAR ( 8000 )
SELECT @SQL = '
DECLARE @TMP TABLE(
[PID_NUM] [INT] NULL,
[ID_NUM] [int] IDENTITY (1, 1) NOT NULL ,
[ ' + @PIDCOL + ' ] [nvarchar] (20) NULL ,
[ ' + @IDCOL + '