SQL选择组织树（层次结构）的查询

Question

我有一个这样的表;

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栏连接同一张表。

任何想法？

---

现在最好的解决办法是

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 

Answer 1

SELECT * FROM TH_ORGANIZATION As O, 
    TH_ORGANIZATION As Sub 
WHERE O.ID_CORGANIZATION 
    = Sub.CORGANIZATION_UPLINK_ID; 

你会得到组织的列表，及其子机构，递归。当然你也可以订购清单。

如果你想在所有的一列，你可以使用一个外部联接这样的（因此只选择O. *）：

SELECT O.* FROM th_organization As O 
LEFT OUTER JOIN th_organization As Sub 
ON O.id_corganization 
    = Sub.corganization_uplink_id; 

示例（我使用的缩写），你应该得到这样的名单：

我发现

id_c uplink_id name 
    1    OrgA 
    2    OrgB 
11  1  subA 
12  1  subB 
21  2  sub2 
111  11  subsubA 

Answer 2

我最好的解决办法：

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 

注： '3' 我开始组织ID