树形结构查询过程
来源:互联网 发布:ubuntu cp 略过目录 编辑:程序博客网 时间:2024/05/21 19:21
USE [Northwind]
GO
/****** 对象: StoredProcedure [dbo].[USP_TREEVIEW] 脚本日期: 12/18/2009 07:59:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[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
EXEC USP_TREEVIEW 'Northwind..Employees','ReportsTo','EmployeeID','LastName'
- 树形结构查询过程
- mysql 树形结构查询(存储过程)
- Oracle树形结构查询
- 树形结构查询
- ORACLE树形结构查询
- oracle树形结构查询
- 查询树形结构
- 树形结构查询
- Oracle树形结构查询
- Oracle查询树形结构
- Oracle树形结构查询
- 循环查询 树形结构
- Oracle查询树形结构
- Oracle查询树形结构
- Orcale查询树形结构
- MyBatis树形结构查询
- oracle 树形结构查询
- /*Oracle树形结构查询*/
- 性能测试——瓶颈分析方法
- ASP.NET中Cookie编程的基础知识
- 【javaweb 学习笔记】 第一课 最简单的登陆
- 图位克隆法
- MII MAC PHY
- 树形结构查询过程
- 把程序当作人生
- asp.net 在IIS下操作Excel文件权限不足的解决方法
- JSP中连接oracle
- neatbean 关于乱码的问题
- LoadRunner测试负载
- 80后的10个人生底线
- MO MT SMS ussd
- ORACLE JOBS