SQL图示树形数据表查询存储过程

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+'] [nvarchar] (20)  NOT NULL ,
    ['+@DESCCOL+'] [ntext]  NULL,
    PATH NVARCHAR(1000) NULL,
    NEXT_PARENTID INT NULL,
     PRE_PARENTID INT NULL,
    PATHCHAR NVARCHAR(1000) DEFAULT('''') NULL,
                ISFIRST BIT DEFAULT(1) NULL
)    
INSERT  @TMP (['+@PIDCOL+'],['+@IDCOL+'],['+@DESCCOL+'])
SELECT ['+@PIDCOL+'],['+@IDCOL+'],['+@DESCCOL+'] FROM '+@TABLE+' order by '+@PIDCOL+','+@IDCOL+'

UPDATE A SET PID_NUM=B.ID_NUM,PATH=CAST(A.ID_NUM AS NVARCHAR),NEXT_PARENTID=B.ID_NUM
FROM @TMP A LEFT JOIN @TMP B ON A.'+@PIDCOL+'=B.'+@IDCOL+'

WHILE @@ROWCOUNT!=0
UPDATE A SET 
A.PATH=ISNULL(CAST(A.NEXT_PARENTID AS NVARCHAR)+''.'','''')+A.PATH
,A.PATHCHAR=CASE WHEN A.ISFIRST=1 THEN
                 CASE WHEN  not exists (select * from @TMP where PID_NUM=A.NEXT_PARENTID and ID_NUM>A.ID_NUM ) THEN
            ''└'' + replicate(''─'', 2) + A.PATHCHAR
                 ELSE
            ''├'' +replicate(''─'', 2) + A.PATHCHAR
                 END
             ELSE
                   CASE WHEN  exists (select * from @TMP where PID_NUM=A.NEXT_PARENTID and ID_NUM>( select ID_NUM from @TMP where ID_NUM=A.PRE_PARENTID) ) THEN
                           ''│'' + replicate(''　'', 2) +A.PATHCHAR
                   ELSE
                          replicate(''　'', 3) +A.PATHCHAR
                   END
                         END
,A.PRE_PARENTID=A.NEXT_PARENTID
,A.NEXT_PARENTID=B.PID_NUM
,A.ISFIRST=0
FROM @TMP A LEFT JOIN @TMP B ON A.NEXT_PARENTID=B.ID_NUM
WHERE A.NEXT_PARENTID IS NOT NULL
--SELECT * FROM @TMP ORDER BY PATH
SELECT PATHCHAR+CAST(['+@DESCCOL+'] AS NVARCHAR) AS '+'[Label_'+@DESCCOL+'],['+@IDCOL+'],['+@PIDCOL+']
FROM @TMP
ORDER BY PATH
'
--print (@SQL)
exec( @SQL)
END

 

3、程序验证

建立存储过程usp_treeview,执行脚本效果如下：

EXEC USP_TREEVIEW 'Northwind..Employees','ReportsTo','EmployeeID','LastName'

Label_LastName	EmployeeID	ReportsTo
Fuller	2	NULL
├──Davolio	1	2
├──Leverling	3	2
├──Peacock	4	2
├──Buchanan	5	2
│　　├──Suyama	6	5
│　　├──King	7	5
│　　└──Dodsworth	9	5
└──Callahan	8	2

4、该存储过程尚未解决问题

     理论上该过程可以显示无限层数据，但仍有些局限性，具体为：

     1、如果父子字段循环引用，即出现图结构，会导致程序陷入死循环

     2、能查出所有层，不能限定层数查询

     3、能查所有结点，不能查询指定子树

     4、查询速度是否还可以优化？

      时间精力有限，存储过程的算法没有详细说明，另外4点扩展问题，并不复杂，相信各位网友一定能完美解决！请诸位代劳吧！