查找树的指定层级,SQL选择查询组织树（层次结构）

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

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.

Any ideas?

Now best solution is

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

Note: '3' my started organization id