/*
SELECT [JX_TargetID]
,[TargetTypeID]
,[TargetName]
,[TargetAttValue]
,[TargetValue]
,[TargetStarttime]
,[TargetEndtime]
,[TargetScope]
,[CheckUserID]
,[BlameUserID]
,[Unit]
,[Description]
,[StatistikCycle]
,[DataSource]
,[StandardLibraryID]
,[GuaranteeMeasures]
,[ParentID]
,[DepartmentID]
,[UserID]
,[Type]
,[CompleteDegree]
,[State]
,[TargetType]
,[CreateerID]
,[EvaluationRules]
,[ExaminationCycle]
,[IsDisplay]
FROM [StandardLibrary_jx].[dbo].[JX_Target]
*/
with temp_JX_Target
as
(
select * from JX_Target where JX_TargetID='C63FDCDA-034E-47B7-9C62-0CF6143ED67A'
union all
select a.* from JX_Target a inner join temp_JX_Target b on a.ParentID=b.JX_TargetID
)
select * from temp_JX_Target
/*
sql 2005 使用 with 进行递归查询
案例：
比如有张表,结构如下:
Example(ID int,ParentID int)
取其某个ID为父节点的树结构的SQL如下：
WITH Example_Table(ID,ParentID)
AS
(
--取根节点放入临时表
SELECT ID,ParentID FROM Example WHERE ID = @ID
--根据已取到的数据递归取其字节点的数据
UNION ALL
SELECT ID,ParentID FROM Example A INNER JOIN Example_Table B ON A.ParentID = B.ID
)
SELECT * FROM Example_Table
with temp_JX_Target
as
(
select * from JX_Target where JX_TargetID='C63FDCDA-034E-47B7-9C62-0CF6143ED67A'
union all
select a.* from JX_Target a inner join temp_JX_Target b on a.ParentID=b.JX_TargetID
)
select * from temp_JX_Target
*/