sql 递归查询

发布时间:2016-12-19 12:10:30编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"sql 递归查询 ",主要涉及到sql 递归查询 方面的内容,对于sql 递归查询 感兴趣的同学可以参考一下。

创建数据库表:

USE [WechatSalesSystem]
GO

/****** Object: Table [dbo].[Employee] Script Date: 2016/12/13 14:41:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[ManagerId] [int] NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nchar](50) NULL,
[Job] [nvarchar](50) NULL,
[ParentId] [int] NULL,
CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED
(
[EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

查询子节点语句:

WITH EmployeeTree
AS (SELECT *
FROM Employee
WHERE EmployeeId =3 --第一个查询作为递归的基点(锚点)
UNION ALL
SELECT Employee.* --第二个查询作为递归成员, 下属成员的结果为空时,此递归结束。
FROM
EmployeeTree INNER JOIN Employee ON EmployeeTree.EmployeeId = Employee.ParentId)
SELECT *
FROM EmployeeTree

查询父节点语句:

with EmployeeTree AS
(
SELECT *,0 level from Employee where EmployeeId=3 
UNION ALL 
SELECT Employee.*,level+1 from EmployeeTree
JOIN Employee on EmployeeTree.parentid= Employee.EmployeeId
)
SELECT * from EmployeeTree where level=2;

查找从子节点到定级节点的路径:

with EmployeeTree as
(
select EmployeeId,ManagerId,FirstName,ParentId,cast(EmployeeId as varchar(100)) as fulltypeid
from Employee where EmployeeId=2--子节点
union all
select
b.EmployeeId,b.ManagerId,b.FirstName,b.ParentId,
cast(a.fulltypeid+','+cast(b.EmployeeId as nvarchar(100)) as varchar(100)) as fulltypeid
from
EmployeeTree a,--子节点数据集
Employee b --父节点数据集
where a.ParentId=b.EmployeeId --子节点数据集.parendID=父节点数据集.ID
)
select * from EmployeeTree ;


上一篇:Android-MediaRecorder-音频录制-警告-W/MediaRecorder(13811): mediarecorder went away with unhandled events
下一篇:maven 阿里镜像

相关文章

关键词: sql 递归查询

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款