100%结贴率游标实现BOM多级查询

2011-06-01 08:38:25 236 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

CTE不是语法糖：T-SQL结构化思维与生产级避坑指南公用表表达式（CTE）是SQL Server中用于逻辑分层、递归计算和可维护性提升的核心结构化工具，其本质是命名的临时结果集而非物理存储，不缓存数据但显著改善查询可读性与调试效率。相比视图和临时表，CTE在递归处理、多引用复用、作用域隔离方面具备不可替代性，尤其适用于用户行为路径分析、BOM物料爆炸、组织架构遍历等典型场景。掌握CTE的执行计划特征、命名规范、MAXRECURSION控制及与窗口函数/表值函数协同技巧，能有效规避生产环境中的死循环、性能骤降与字段污染等高频问题。阅读详情

USE db
go
if object_id('#t1') IS NOT NULL
drop table #t1
go

create table #t1
(
parentID varchar(40),
ID varchar(40),
Lever int,
Name varchar(64)
)

第一层：
Insert into #t values(‘2012’,’909’,1,’ 气缸盖总成’)
第二层：
Insert into #t values(‘909’,’929’,2,’ 阀体’)
Insert into #t values(‘909’,’1709’,2,’ 方螺母2’)
第三层：
Insert into #t values(‘929’,’ 1711’,3,’ 阀’)

第一层：
parentID ID lever name
2012 909 1 气缸盖总成

第二层：
parentID ID lever name
909 929 2 阀体
909 1709 2 方螺母2

第三层：
parentID ID lever name
929 1711 3 阀

想要显示如下：
parentID ID lever name
2012 909 1 气缸盖总成
2012 929 2 阀体
2012 1709 2 方螺母2
2012 1711 3 阀

...全文

升降机SEW MOVIDRIVE变频器设置选择PO1 第2,3,9,13 位，点击“SendPA”Start reference，编码器反馈值为0（PI2/3 Actual position=0）提示：传送参考位置前请断开变频器安全24V电源，否则会弹出禁止修改提示框；提示：做这一步时，如果出现电机不能转动，或产生报警不能通过编码器匹配。下载配置的参数，提示：下载参数需要断开变频器的安全24V电源。编码器匹配通过后点击下一步，进入下图画面传送参考位置。1电机不能转动：a可能是由于变频器的安全24V未给。点击2.SEW总线扩展定位，定位块设置。阅读详情

sadikaqy

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

htl258_Tony 2011-06-02

打赏
举报

[Quote=引用 10 楼 sadikaqy 的回复:]
引用 9 楼 abcjun188 的回复:
SQL code
if object_id('tempdb..#t') IS NOT NULL
drop table tempdb..#t
go

create table #t
(
parentID varchar(40),
ID varchar(40),
Lever int,
Name varchar(64)
)

--第……
[/Quote]可以把自定义函数改为存储过程

AcHerat 元老 2011-06-01

打赏
举报

楼主适当改里面的参数就可以，对应表查询也要改动。



create table tb

(

parentid varchar(40),

id varchar(40),

lever int,

name varchar(64)

)



--第一层：

insert into tb values('2012','909',1,'气缸盖总成')

--第二层：

insert into tb values('909','929',2,'阀体')

insert into tb values('909','1709',2,'方螺母2')

--第三层：

insert into tb values('929','1711',3,'阀')

go



create function f_getid(@id varchar(40))

returns @t table(parentid varchar(40),id varchar(40),lever int,name varchar(64))

as

begin

   insert @t select parentid,id,lever,name from tb where id = @id

   while @@rowcount<>0

   begin

      insert @t select b.parentid,a.id,a.lever,a.name from tb a inner join @t b

      on a.parentid = b.id and 

      not exists(select 1 from @t where id = a.id)

   end 

return

end

go

select * from dbo.f_getid('909')





drop function dbo.f_getid

drop table tb



/*



parentid                                 id                                       lever       name

---------------------------------------- ---------------------------------------- ----------- ----------------------------------------------------------------

2012                                     909                                      1           气缸盖总成

2012                                     929                                      2           阀体

2012                                     1709                                     2           方螺母2

2012                                     1711                                     3           阀



(4 行受影响)

liang145 2011-06-01

打赏
举报



if object_id('t1') IS NOT NULL

drop table t1

go

create table t1

(

parentID varchar(40),

ID varchar(40),

Lever int,

[Name] varchar(64)

)

Insert into t1 values('2012','909',1,'气缸盖总成')

Insert into t1 values('909','929',2,'阀体')

Insert into t1 values('909','1709',2,'方螺母2')

Insert into t1 values('929','1711',3,'阀')



Insert into t1 values('255','654',1,'气缸盖总成')

Insert into t1 values('654','789',2,'阀体')

Insert into t1 values('654','7855',2,'方螺母2')

Insert into t1 values('789','3333',3,'阀')



create function [dbo].[GetRoot](@ID nvarchar(40)) returns nvarchar(40)

as

begin

  declare @R as nvarchar(40)

  declare @parentID as nvarchar(40)

  declare @pLever as int

  select @parentID=parentID,@pLever=Lever from t1 where ID=@ID

  if @pLever=1

    begin  set @R=@parentID  end

  else

    begin  set @R=dbo.GetRoot(@parentID)  end

  return @R

end



select dbo.GetRoot(ID) as [Root],* from t1

--小F-- 2011-06-01

打赏
举报

-->Title:Generating test data

-->Author:wufeng4552

-->Date :2009-09-30 08:52:38

set nocount on

if object_id('tb','U')is not null drop table tb

go

create table tb(ID int, ParentID int)

insert into tb select 1,0  

insert into tb select 2,1  

insert into tb select 3,1  

insert into tb select 4,2  

insert into tb select 5,3  

insert into tb select 6,5  

insert into tb select 7,6

-->Title:查找指定節點下的子結點

if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID

go

create function Uf_GetChildID(@ParentID int)

returns @t table(ID int)

as

begin

   insert @t select ID from tb where ParentID=@ParentID

   while @@rowcount<>0

   begin

      insert @t select a.ID from tb a inner join @t b

      on a.ParentID=b.id and 

      not exists(select 1 from @t where id=a.id)

   end 

return

end

go

select * from dbo.Uf_GetChildID(5)

/*

ID

-----------

6

7

*/

-->Title:查找指定節點的所有父結點

if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID

go

create function Uf_GetParentID(@ID int)

returns @t table(ParentID int)

as

begin

   insert @t select ParentID from tb where ID=@ID

   while @@rowcount!=0

   begin

     insert @t select a.ParentID from tb a inner join @t b

       on a.id=b.ParentID and 

       not exists(select 1 from @t where ParentID=a.ParentID)

   end

  return

end

go

select * from dbo.Uf_GetParentID(2)

/*

ParentID

-----------

1

0

*/







本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx

sadikaqy 2011-06-01

打赏
举报

[Quote=引用 2 楼 htl258 的回复:]
实现多级查询不一定要游标
[/Quote]
用的是sql2000数据库。。。

AcHerat 元老 2011-06-01

打赏
举报

有时复制粘贴并不是为了纯粹的接分，只是方法总结的比较全，楼主已经知道你要的是BOM节点查询，为什么不在CSDN搜搜相关帖子呢？



if object_id('tb_bookInfo') is not null drop table tb_bookInfo

go

create table tb_bookInfo(number int,name varchar(10),type int)

insert tb_bookInfo

select 1 ,'n1', 6 union all

select 2 ,'n2', 3





if object_id('tb_bookType') is not null drop table tb_bookType

go

create table tb_bookType(id int,typeName varchar(10),parentid int)

insert tb_bookType

select 1,'英语',0 union all

select 2,'生物',0 union all

select 3,'计算机',0 union all

select 4,'口语',1 union all

select 5,'听力',1 union all

select 6,'数据库',3 union all

select 7,'软件工程',3 union all

select 8,'SQL Server',6



select a.*,b.level from tb_bookInfo  a,f_getC(3) b  where a.type=b.id  order by b.level 

/*

number      name       type        level      

----------- ---------- ----------- -----------

2           n2         3           0

1           n1         6           1



（所影响的行数为 2 行）

*/

--查所有父结点

if object_id('f_getP') is not null drop function f_getP

go

create function f_getP(@id int) 

returns @re table(id int,level int) 

as 

begin

    declare @l int 

    set @l=0 

    insert @re select @id,@l 

    while @@rowcount>0 

    begin 

 set @l=@l+1

 insert @re select a.parentid,@l from tb_bookType a,@re b

 where a.id=b.id and b.level=@l-1 and a.parentid<>0

    end 

    update @re set level=@l-level

    return 

end 

go 





--查所有子结点

if object_id('f_getC') is not null drop function f_getC

go

create function f_getC(@id int) 

returns @re table(id int,level int) 

as 

begin

    declare @l int 

    set @l=0 

    insert @re select @id,@l 

    while @@rowcount>0

    begin 

        set @l=@l+1

        insert @re select a.id,@l from tb_bookType as a,@re as b 

 where b.id=a.parentid and b.level=@l-1

    end

    return 

end 

go 



--查所有父子结点

if object_id('f_getAll') is not null drop function f_getAll

go

create function f_getAll(@id int) 

returns @re table(id int,level int) 

as 

begin 

    declare @l int 

    set @l=0 

    insert @re select @id,@l 

    while @@rowcount>0 

    begin 

 set @l=@l+1

 insert @re select a.parentid,@l from tb_bookType a,@re b

 where a.id=b.id and b.level=@l-1 and a.parentid<>0

    end 

    update @re set level=@l-level 

    while @@rowcount>0

    begin 

        set @l=@l+1

        insert @re select a.id,@l from tb_bookType as a,@re as b 

 where b.id=a.parentid and b.level=@l-1

    end

    return

end 

go  

 



--删除演示



drop table tb_bookInfo



drop table tb_bookType



drop function f_getP



drop function f_getC

drop function f_getAll

GO



--sqlserver2005的新方法



-- 建立演示环境

IF OBJECT_ID('[Dept]') IS NOT NULL

    DROP TABLE [Dept]

GO

CREATE TABLE Dept(

 id int PRIMARY KEY,

 parent_id int,

 name nvarchar(20))

INSERT Dept

SELECT 1, 0, N'财务部' UNION ALL

SELECT 2, 0, N'行政部' UNION ALL

SELECT 3, 0, N'业务部' UNION ALL

SELECT 4, 0, N'业务部' UNION ALL

SELECT 5, 4, N'销售部' UNION ALL

SELECT 6, 4, N'MIS' UNION ALL

SELECT 7, 6, N'UI' UNION ALL

SELECT 8, 6, N'软件开发' UNION ALL

SELECT 9, 8, N'内部开发'

GO

--1、父-〉子

-- 查询指定部门下面的所有部门

DECLARE @Dept_name nvarchar(20)

SET @Dept_name = N'MIS'

;WITH

DEPTS AS(

 -- 定位点成员

 SELECT * FROM Dept WHERE name = @Dept_name

 UNION ALL

 -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归

 SELECT A.*  FROM Dept A, DEPTS B  WHERE A.parent_id = B.id

)

SELECT * FROM DEPTS

GO

--结果如下

/*

id          parent_id   name                

----------- ----------- --------------------

6           4           MIS

7           6           UI

8           6           软件开发

9           8           内部开发



（所影响的行数为 4 行）

*/



--2、子-〉父

-- 查询指定部门下面的所有部门

DECLARE @Dept_name nvarchar(20)

SET @Dept_name = N'内部开发'

;WITH

DEPTS AS(

 -- 定位点成员

  SELECT * FROM Dept WHERE name = @Dept_name

--SELECT d.id,d.parent_id,d.name,convert(nvarchar(50),d.name) as parent  FROM Dept where @Dept_name

 UNION ALL

 -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归

 SELECT a.* FROM Dept a, DEPTS b WHERE a.id = b.parent_id

)

SELECT * FROM DEPTS

GO



--结果如下

/*

id          parent_id   name                

----------- ----------- --------------------

9           8           内部开发

8           6           软件开发

6           4           MIS

4           0           业务部



（所影响的行数为 4 行）

*/



-- 删除演示环境

DROP TABLE Dept

htl258_Tony 2011-06-01

打赏
举报

实现多级查询不一定要游标

htl258_Tony 2011-06-01

打赏
举报

if object_id('tempdb..#t') IS NOT NULL

drop table tempdb..#t

go



create table #t

(

parentID varchar(40),

ID varchar(40),

Lever int,

Name varchar(64)

)



--第一层：

Insert into #t values('2012','909',1,'气缸盖总成')

--第二层：

Insert into #t values('909','929',2,'阀体')

Insert into #t values('909','1709',2,'方螺母2')

--第三层：

Insert into #t values('929','1711',3,'阀')



--SELECT * FROM #t



;WITH t AS (

	SELECT parent=parentid,* FROM #t WHERE lever=1

	UNION ALL

	SELECT parent,b.* FROM t a JOIN #t b ON a.id=b.parentID

)

SELECT parentid=parent,id,lever,name FROM t

/*

parentid	id	lever	name

2012	909	1	气缸盖总成

2012	929	2	阀体

2012	1709	2	方螺母2

2012	1711	3	阀

*/

刚把贴子找出来你又发了

sadikaqy 2011-06-01

打赏
举报

[Quote=引用 9 楼 abcjun188 的回复:]
SQL code
if object_id('tempdb..#t') IS NOT NULL
drop table tempdb..#t
go

create table #t
(
parentID varchar(40),
ID varchar(40),
Lever int,
Name varchar(64)
)

--第一层：
Insert into #t val……
[/Quote]
sql2000的数据库，游标或存储过程可以实现吗？

打一壶酱油 2011-06-01

打赏
举报

if object_id('tempdb..#t') IS NOT NULL

drop table tempdb..#t

go



create table #t

(

parentID varchar(40),

ID varchar(40),

Lever int,

Name varchar(64)

)



--第一层：

Insert into #t values('2012','909',1,'气缸盖总成')

--第二层：

Insert into #t values('909','929',2,'阀体')

Insert into #t values('909','1709',2,'方螺母2')

--第三层：

Insert into #t values('929','1711',3,'阀')



--SELECT * FROM #t



;WITH t AS (

    SELECT parent=parentid,* FROM #t WHERE lever=1

    UNION ALL

    SELECT parent,b.* FROM t a JOIN #t b ON a.id=b.parentID

)

SELECT parentid=parent,id,lever,name FROM t

/*

parentid    id    lever    name

2012    909    1    气缸盖总成

2012    929    2    阀体

2012    1709    2    方螺母2

2012    1711    3    阀

*/





顶一下

sadikaqy 2011-06-01

打赏
举报

游标可以实现吗？

100%结贴率 游标实现BOM多级查询

相关推荐

100%结贴率游标实现BOM多级查询