重工电子论坛

标题: 广怀数据库查询相关 [打印本页]

作者: 李维强-15级 时间: 2022-8-16 11:55
标题: 广怀数据库查询相关
本帖最后由李维强-15级于 2022-8-19 16:32 编辑

递归查询部门名称

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

BEGIN

WITH CTE AS(

SELECT ID,depNum,depName,depOwnerName,depName as UpDepName FROM t_b_Department WHERE ParentDepId=0

UNION ALL

SELECT b.ID,b.depNum,b.depName,b.depOwnerName,p.depName as UpDepName FROM CTE p INNER JOIN t_b_Department b ON p.ID=b.ParentDepId

)

SELECT depNum,depName,depOwnerName,UpDepName FROM CTE

END

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

WITH CTE AS

(

SELECT ID,depNum,depName,depOwnerName,depName as UpDepName FROM t_b_Department WHERE ParentDepId=79 AND ID=70

UNION ALL

SELECT b.ID,b.depNum,b.depName,b.depOwnerName,p.depName as UpDepName FROM CTE p INNER JOIN t_b_Department b ON p.ID=b.ParentDepId

)

SELECT ID,depNum,depName,depOwnerName,UpDepName FROM CTE

欢迎光临重工电子论坛 (http://cqutlab.cn/)