广度优先的sql树形查询
来源:互联网 发布:java求1000以内的质数 编辑:程序博客网 时间:2024/06/15 18:34
/*
注解:
以前遇到过一个同事,在处理树的时候,在sql语句里面用递归,造成性能非常低下。
在遇到sql处理树的时候,可以采用以下方法,用循环来解决。
主要思路:
找到Cateogry的children插入临时表,在临时表里做遍历,每到一条记录,都做一个操作:将它的children select 出来,插入临时表,最后,将临时表join Cateogry表。
*/
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Proc_GetOffspringCategoryList]
@CategoryID uniqueidentifier,
@CategoryStandardID uniqueidentifier
AS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID=object_id(N'#_Name') AND OBJECTPROPERTY(id, N'IsUserTable')=1)
DROP TABLE #_Name
CREATE TABLE [dbo].[#_Name] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL,
[ParentCategoryID] uniqueidentifier NULL ,
[CategoryID] uniqueidentifier NULL ,
[OrderNum] [int] NULL
) ON [PRIMARY]
/*do insert*/
IF @CategoryID is null
begin
INSERT INTO #_Name
SELECT
[Category].[ParentCategoryID],
[Category].[ID] AS CateogryID,
[Category].[OrderNum]
FROM
[Category]
WHERE
[Category].[ParentCategoryID] is null AND
[Category].[CategoryStandardID] = @CategoryStandardID
order by [Category].[OrderNum]
end
else
begin
INSERT INTO #_Name
SELECT
[Category].[ParentCategoryID],
[Category].[ID] AS CateogryID,
[Category].[OrderNum]
FROM
[Category]
WHERE
[Category].[ParentCategoryID] = @CategoryID AND
[Category].[CategoryStandardID] = @CategoryStandardID
order by [Category].[OrderNum]
end
DECLARE @ID BIGINT
SET @ID = 1
DECLARE @Parent uniqueidentifier
SET @Parent = (SELECT TOP 1 [CategoryID] FROM #_Name WHERE ID = @ID)
WHILE (@Parent IS NOT NULL)
BEGIN
INSERT INTO #_Name
SELECT
[Category].[ParentCategoryID],
[Category].[ID] as CategoryID,
[Category].[OrderNum]
FROM
[Category]
WHERE
[Category].[ParentCategoryID] = @Parent AND
[Category].[CategoryStandardID] = @CategoryStandardID
order by [Category].[OrderNum]
SET @ID = @ID + 1
SET @Parent = (SELECT TOP 1 [CategoryID] FROM #_Name WHERE ID = @ID)
END
/*end do insert*/
SELECT [Category].*
FROM #_Name
JOIN Category ON [#_Name].[CategoryID] = [Category].[ID]
注解:
以前遇到过一个同事,在处理树的时候,在sql语句里面用递归,造成性能非常低下。
在遇到sql处理树的时候,可以采用以下方法,用循环来解决。
主要思路:
找到Cateogry的children插入临时表,在临时表里做遍历,每到一条记录,都做一个操作:将它的children select 出来,插入临时表,最后,将临时表join Cateogry表。
*/
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Proc_GetOffspringCategoryList]
@CategoryID uniqueidentifier,
@CategoryStandardID uniqueidentifier
AS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID=object_id(N'#_Name') AND OBJECTPROPERTY(id, N'IsUserTable')=1)
DROP TABLE #_Name
CREATE TABLE [dbo].[#_Name] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL,
[ParentCategoryID] uniqueidentifier NULL ,
[CategoryID] uniqueidentifier NULL ,
[OrderNum] [int] NULL
) ON [PRIMARY]
/*do insert*/
IF @CategoryID is null
begin
INSERT INTO #_Name
SELECT
[Category].[ParentCategoryID],
[Category].[ID] AS CateogryID,
[Category].[OrderNum]
FROM
[Category]
WHERE
[Category].[ParentCategoryID] is null AND
[Category].[CategoryStandardID] = @CategoryStandardID
order by [Category].[OrderNum]
end
else
begin
INSERT INTO #_Name
SELECT
[Category].[ParentCategoryID],
[Category].[ID] AS CateogryID,
[Category].[OrderNum]
FROM
[Category]
WHERE
[Category].[ParentCategoryID] = @CategoryID AND
[Category].[CategoryStandardID] = @CategoryStandardID
order by [Category].[OrderNum]
end
DECLARE @ID BIGINT
SET @ID = 1
DECLARE @Parent uniqueidentifier
SET @Parent = (SELECT TOP 1 [CategoryID] FROM #_Name WHERE ID = @ID)
WHILE (@Parent IS NOT NULL)
BEGIN
INSERT INTO #_Name
SELECT
[Category].[ParentCategoryID],
[Category].[ID] as CategoryID,
[Category].[OrderNum]
FROM
[Category]
WHERE
[Category].[ParentCategoryID] = @Parent AND
[Category].[CategoryStandardID] = @CategoryStandardID
order by [Category].[OrderNum]
SET @ID = @ID + 1
SET @Parent = (SELECT TOP 1 [CategoryID] FROM #_Name WHERE ID = @ID)
END
/*end do insert*/
SELECT [Category].*
FROM #_Name
JOIN Category ON [#_Name].[CategoryID] = [Category].[ID]
原文链接:http://www.cnblogs.com/jinweijie/archive/2007/05/16/748570.html
- 广度优先的sql树形查询
- 图的广度优先
- 从数据库读取树形资料,并在treeview中显示,非常精练的广度优先算法
- Linux文件系统调用----实现对树形文件结构的广度优先遍历,即按层输出文件信息
- 图-无权图-广度优先查询(BFS)
- 有关树形结构的查询--Sql
- 有关树形结构的查询--Sql
- SQL 中树形结构查询的运用
- 图的深度优先,广度优先
- 数的广度优先和深度优先
- 图的遍历-(深度优先&广度优先)
- 深度优先和广度优先的理解
- 图的深度优先和广度优先
- 图的遍历:深度优先、广度优先
- 广度优先搜索的思想
- 图的广度优先遍历
- 图的广度优先遍历
- 图的广度优先搜索
- Spring的事务管理
- 李开复谏言学生转学Android软件开发
- 视频播放的基本原理【转贴】
- 解决新内核不能启动问题:kernel panic - not syncing: VFS: unable to mount root fs on unkno
- DB2中几种遇到的SQL1032N出错的解决
- 广度优先的sql树形查询
- nginx linux内核参数
- 将内存位图写入到BMP文件中
- Weblogic - Failed to bind remote object 错误解决方法
- delphi判断电脑是否安装了excel
- 分享20个漂亮强大的jQuery导航菜单
- 图像图形资料
- PermissionService.java (编号:20110621A1130)
- jquery 二级导航 兼容ie6