SQL Server树查询 [英] SQL Server Tree Query

问题描述

我需要一些帮助，是MS SQL Server查询.我不是DBA.我有一个带有组织表的应用程序，该表由父子关系组成:

I need some help is MS SQL Server Query. I’m not much of a DBA. I have an application with an Organization Table which is made up of a parent-child relationship:

CREATE TABLE [dbo].[Organizations](
    [OrgPK] [int] IDENTITY(1,1) NOT NULL,
    [OrgParentFK] [int] NULL,
    [OrgName] [varchar](200) NOT NULL,
CONSTRAINT [PK__Organizations] PRIMARY KEY CLUSTERED

样本数据如下:

OrgPK,   OrgParentFK, OrgName
1,  0,  Corporate
2,  1,  Department A
3,  1,  Department B
4,  2,  Division 1
5,  2,  Division 2
6,  3,  Division 1
7,  6,  Section 1
8,  6,  Section 2

我正在尝试生成一个查询，该查询返回基于给定OrgPK的组织路径.例如，如果给定OrgPK = 7，则查询将返回公司/部门B/部门1/部门1"

I'm trying to generate a query that returns an org path based on a given OrgPK. Example if given OrgPK = 7 the query would return 'Corporation/Department B/Division 1/Section 1'

如果给定OrgPk = 5，则返回字符串为公司/部门A/部门2"

If give OrgPk = 5 the return string would be 'Corporation/Department A/Division 2'

谢谢您的帮助.

推荐答案

WITH  OrganizationsH (OrgParentFK, OrgPK, OrgName, level, Label) AS
(
    SELECT OrgParentFK, OrgPK, OrgName, 0, CAST(OrgName AS VARCHAR(MAX)) As Label
    FROM Organizations
    WHERE OrgParentFK IS NULL
    UNION ALL
    SELECT o.OrgParentFK, o.OrgPK, o.OrgName, level + 1,  CAST(h.Label + '/' + o.OrgName  VARCHAR(MAX)) As Label
    FROM Organizations o JOIN OrganizationsH h ON o.OrgParentFK = h.OrgPK
)

SELECT OrgParentFK, OrgPK, OrgName, level, Label
FROM OrganizationsH
WHERE OrgPK = 5

h/t到marc_s

h/t to marc_s

这篇关于SQL Server树查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！