SQL Select查询组织树(层次结构) [英] SQL Select Query for organization tree (hierarchy)

问题描述

我有一张这样的桌子；

I have a table like this;

CREATE TABLE [dbo].[TH_ORGANIZATION]
(
    [ID_CORGANIZATION] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
    [ID_CCOMPANY] [nvarchar](10) NOT NULL,
    [CORGANIZATION_NAME_tr] [nvarchar](100) NULL,
    [CORGANIZATION_NAME_en] [nvarchar](100) NULL,
    [CORGANIZATION_MAN_ID_CEMP] [decimal](10, 0) NULL,
    [CORGANIZATION_UPLINK_ID] [decimal](18, 0) NULL,
    [CACTIVE] [bit] NOT NULL CONSTRAINT [DF_TH_ORGANIZATION_CACTIVE]  DEFAULT ((1)),

    CONSTRAINT [PK_TH_ORGANIZATION] 
       PRIMARY KEY CLUSTERED ([ID_CORGANIZATION] 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

现在，我想编写一个SQL查询来获取我的组织树.主列为ID_CORGANIZATION，子组织与CORGANIZATION_UPLINK_ID列连接在同一表中.

Now I want to write a SQL query for getting my organization tree. The main column is ID_CORGANIZATION and the sub organziation connected with CORGANIZATION_UPLINK_ID column with the same table.

有什么想法吗?

现在最好的解决方案是

 WITH temp as(SELECT * FROM TH_ORGANIZATION WHERE ID_CORGANIZATION = '3'  UNION ALL SELECT ei.* FROM TH_ORGANIZATION ei INNER JOIN temp x ON ei.CORGANIZATION_UPLINK_ID = x.ID_CORGANIZATION ") SELECT * FROM temp

推荐答案

我找到的最佳解决方案:

My best solution that I find :

WITH temp as(SELECT * FROM TH_ORGANIZATION WHERE ID_CORGANIZATION = '3'  UNION ALL SELECT ei.* FROM TH_ORGANIZATION ei INNER JOIN temp x ON ei.CORGANIZATION_UPLINK_ID = x.ID_CORGANIZATION ") SELECT * FROM temp

注意:我开始的组织ID为'3'

Note: '3' my started organization id

这篇关于SQL Select查询组织树(层次结构)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！