-
SQL语句对表中父子节点正向和反向取所有节点
CREATE TABLE [dbo].[temptb](
[id] [int] IDENTITY(1,1) NOT NULL,
[pid] [int] NULL,
[name1] [varchar](20) ,
[name] [nvarchar](50) ,
[parentid] [int] NULL,
CONSTRAINT [PK_temptb] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//* 创建函数 根据节点id找出其所有父节点*/
create function f_pid(@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.pid,@l
from temptb a,@re b
where a.id=b.id
and b.level=@l-1
and a.pid<>0
end
update @re set level=@l-level
return
end
go
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//* */
select a.*,b.level
from temptb a,f_pid(7) b
where a.id=b.id
order by b.level
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//* 创建函数 根据节点id 找出所有子节点*/
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
create function c_tree(@initid int)/**//*定义函数c_tree,输入参数为初始节点id*/
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
returns @t table(id int,name varchar(100),parentid int,lev INT,byid int)/**//*定义表t用来存放取出的数据*/
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
declare @i int/**//*标志递归级别*/
set @i=1
insert @t select id,name,parentid,@i ,byid=@initid from temptb where id=@initid
while @@rowcount<>0
begin
set @i=@i+1
insert @t select a.id,a.name,a.parentid,@i,@initid from temptb as a,@t as b
where b.id=a.parentid and b.lev=@i-1
end
return
END
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*在上面的函数中由于表变量使用了两次,性能很差 ,下面的性能要高些*/
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create function [dbo].[UF_GetOwnerSKUNumber]()
RETURNS @b table(id int,byid int)
BEGIN
DECLARE @t table(id int,lev INT,byid int)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
declare @i int/**//*标志递归级别*/
set @i=1
insert @t select c.id,@i ,c.byid
from [temptb] c WITH (NOLOCK)
WHERE [pid]=0 OR [parentid] IS NULL
OR parentid NOT IN (SELECT id FROM [temptb]WHERE id=c.id)
while @@rowcount<>0
begin
set @i=@i+1
insert @b SELECT a.id,b.byid from
[temptb] as a WITH (NOLOCK) ,@t as b
where b.id=a.parentid and b.lev=@i-1
end
RETURN
END
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select * from c_tree( )
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//* 把所有行转换为一个字符串 */
DECLARE @FileClassName nvarchar(max)
SET @FileClassName=''
SELECT @FileClassName =+ @FileClassName+CONVERT(varchar(20),id)+',' FROM [temptb] a WHERE pid=0
SELECT @FileClassName AS a
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-
相关阅读:
redis集群搭建
mybatis逆向工程--自动生成实体代码(mybatis-generator)
设置启用mysql慢查询日志
mysql 删除重复数据
Vue自定义指令--开发一个可从外部关闭的下拉菜单
《Vue.js实战》章七 组件——标签页组件:思路详解
《Vue.js实战》章七 组件——数字输入框组件
托管在github上的个人简历、静态网页里的音乐播放器,暂停、下一首、顺序播放
2019.5.24 自学前端 120天
《超实用的jQuery代码段》-3:计算加载时间、模拟抽奖、规定年龄计算、通用的清空表格函数
-
原文地址:https://www.cnblogs.com/ghd258/p/1188592.html
Copyright © 2020-2023
润新知