SQL server 根据子级查询根父级

小编做惯了根据父级查子级，可是怎么也没想到有一天会遇到根据子级查根父级这种需求，小编起初想通过C#，但是小编最后并没有做到合适的方法，那小编只能通过SQL server的方式来解决了
推算历程一：

with cte(FieldID,ParentId,FieldName)
as
(--下级父项
select FieldID,ParentId,FieldName from IntellManuSchool_Field where FieldID in (1,10) --子级id
union all
--递归结果集中的父项
select t.FieldID,t.ParentId,t.FieldName from IntellManuSchool_Field as t
inner join cte as c on t.FieldID = c.ParentId

)
select FieldID,ParentId,FieldName from cte where ParentId=0 group by FieldID,ParentId,FieldName order by FieldID asc

小编在网上查询很多后都会遇到该方法，小编使用该方法虽然查到的是正确的，但是此方法对连表查询并不友好，他需要定义一个变量，然后通过变量（子级）查询，却没办法直接连表查询所有的
推算历程二：

select child.FieldID as ChildFieldID,Parent.FieldID as ParentFieldID,
Parent.ParentId,Parent.FieldName as ParentName from IntellManuSchool_Field as Parent 
inner join IntellManuSchool_Field as child on Parent.FieldID=child.ParentId 
where Parent.ParentId=0 
group by child.FieldID ,Parent.FieldID ,Parent.ParentId,Parent.FieldName--该方法已经可以查询所有子类对应父类的列表

小编查来查去居然查到了小编之前的一篇博客Sql server 同一张表中ID,Name,ParentID要求同时查出ID对应的Name,和ParentID对应的Name，并从中得到了启发，方式如上代码，不断的总结还是很好的，因为自己也不知道哪天又会遇到此类问题

下面是小编最后的关联，table逻辑很复杂，小编只是写了一个代替，由于一行数据最终存在含有两个一级类型的现象，所以需要通过stuff的方式实现

select * from table a left join 
(
	SELECT PackegId,Title,Field,
	STUFF((SELECT ','+ParentName FROM (select PackegId,Title,Field,ParentName from IntellManuSchool_CoursePackage as a 
	left join  (select child.FieldID as ChildFieldID,Parent.FieldID as ParentFieldID,Parent.ParentId,
	Parent.FieldName as ParentName from IntellManuSchool_Field as Parent 
	inner join IntellManuSchool_Field as child on Parent.FieldID=child.ParentId 
	where Parent.ParentId=0 group by child.FieldID ,Parent.FieldID ,Parent.ParentId,Parent.FieldName ) as b 
	on CHARINDEX(','+LTRIM(b.ParentFieldID)+',',','+a.Field+',')>0 or CHARINDEX(','+LTRIM(b.ChildFieldID)+',',','+a.Field+',')>0 
	group by PackegId,Title,Field,ParentName) as a WHERE PackegId=T.PackegId FOR XML PATH('')),1,1,'') AS ParentName  
	FROM (select PackegId,Title,Field,ParentName from IntellManuSchool_CoursePackage as a 
	left join  (select child.FieldID as ChildFieldID,Parent.FieldID as ParentFieldID,Parent.ParentId,Parent.FieldName as ParentName from IntellManuSchool_Field as Parent 
	inner join IntellManuSchool_Field as child on Parent.FieldID=child.ParentId where Parent.ParentId=0 
	group by child.FieldID ,Parent.FieldID ,Parent.ParentId,Parent.FieldName ) as b 
	on CHARINDEX(','+LTRIM(b.ParentFieldID)+',',','+a.Field+',')>0 or CHARINDEX(','+LTRIM(b.ChildFieldID)+',',','+a.Field+',')>0 
	group by PackegId,Title,Field,ParentName) T GROUP BY PackegId,Title,Field
) i on a.PackegId = i.PackegId
where  a.PackegId in (3,4,5,6,8,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28,29,30,31,32,33,34,35,36,37,
38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,55,56,57,58,59,60,61,62,63,64,65,66,67,69,70,71,72,73,74,75,
76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,1103,1104)

顺便讲讲如何在存储过程中找找自己的错误代码问题技巧，首先通过print @theStr可以打印变量的值，当变量为SQL server语句时该方法还是屡试不爽的，再者通过exec 存储过程名 参数可以执行存储过程，两者结合找存储过程的报错问题就会效率很高

最近心得：作为一名全栈，小编真的是很累，忙完前台忙后台，忙完后台忙数据库，不管哪一种隔一段时间没写，再突然安排该需求时，小编就突然需要花一段时间才能适应，短则2-3天，长则5-6天，不过随着越做越多，小编觉得越到后面数据库永远都是最重要的内容，因为前端后端怎么实现的可以照搬照抄，而数据库越到后面就越是在和逻辑打交道，像极了高中数学考试

2022-08-19更新，小编今天又发现一种更简单的查询方法子查询

select a.PackegId,a.Title,a.Field,a.StarRating,isnull(b.StudyUserCount,0) as StudyUserCount,a.AddTime,
(SELECT   STUFF
 ((SELECT   ',' + CONVERT(VARCHAR, FieldName)
   FROM IntellManuSchool_Field WHERE   charindex(',' + ltrim(FieldID) + ',', ',' +
     (SELECT   STUFF
        ((SELECT   ',' + CONVERT(VARCHAR, ParentId) FROM IntellManuSchool_Field WHERE   charindex(',' + ltrim(FieldID) + ',', ',' + a.Field + ',') > 0 AND FieldType = 1 FOR XML PATH('')), 1, 1, '')) + ',') > 0 AND FieldType = 0 FOR XML PATH('')), 1, 1, '')) AS OneLevelField  from IntellManuSchool_CoursePackage as a

今日心得：逻辑的美就在于，原理很简单，但是做起来却有千万般变化，谁能在看到需求后想到最合适的原理和逻辑去实现，并使用最简单的方法做出程序，谁就是开发最屌的