[求教]疑窦SQL语句求解

[求教]疑难SQL语句求解
这里有三张表，分别是：课程表、学员表、关系表；其他地方使用查询都可以，问题较难的地方是领导让显示课程和学员有隶属关系的，显示大分类和二级分类以后，要显示学员，如果学员隶属于更下一层的也要显示进来，如下表：

课程表--------------------------------------------
ID 名称 类型 层级
1 教学计划 JH 1
2 课程 KC 2
3 制图设计课程 KC 3
4 程序设计课程 KC 3
5 HTML课程 KC 3
6 CSS课程 KC 3
7 培训 PX 2
8 礼仪培训 PX 3
9 消防培训 PX 3
10 反恐培训 PX 3
11 各类灭火器使用培训 PX 4
12 HTML编写规范课程 KC 4
13 JS课程 KC 4
14 JQUERY课程 KC 4
15 接待礼仪 PX 4


学员表--------------------------------------------
ID 名称
1 刘德华
2 张学友
3 梁朝伟
4 郭富城
5 刘青云
6 黎明
7 张曼玉
8 王祖贤
9 容祖儿
10 任贤齐


关系表--------------------------------------------
父ID 成员ID 关系类型
1 2 教学
1 7 教学
2 3 教学
2 4 教学
2 5 教学
2 6 教学
7 8 教学
7 9 教学
7 10 教学
7 11 教学
5 12 教学
4 13 教学
4 14 教学
9 11 教学
8 15 教学
3 1 学员
4 2 学员
5 3 学员
6 4 学员
8 5 学员
9 6 学员
10 7 学员
11 8 学员
12 9 学员
13 10 学员
14 7 学员
15 8 学员


要求搜索结果如下：

课程 制图设计课程 刘德华
程序设计课程 张学友
任贤齐
张曼玉
HTML课程 梁朝伟
容祖儿
CSS课程 郭富城
培训 礼仪培训 刘青云
王祖贤
消防培训 黎  明
王祖贤
反恐培训 张曼玉


请各位高手指教一下，感谢!

------解决思路----------------------
写个函数，输入课程ID，如果他的层级小于等于3 返回本身。否则返回他的第三层父ID，

create function [dbo].[mytest] (@id1 int )
returns  int 
 as 
 begin 
   declare @level int,@id2 int
   set @id2=@id1
   select @level=层级 from 课程表 where id=@id1
   while @level>3 
     begin 
        select @level=a.层级,@id2=a.ID from 课程表  as a join 关系表 as b
        on a.ID=b.父ID and b.子ID=@id1  
     end
    return @id2 
  end
GO

--代码
[code=sql]
select b.名称,c.名称 from (select dbo.mytest(父ID) as  父ID ,子ID from 关系表 as a 

where a.关系类型='学员') as a join 学员表 as b on a.子ID=b.ID
join 课程表 as c on a.父ID=c.ID
order by c.ID

[/code]
具体聚合 导出处理下。，至于这些课程的父ID（第二层） 匹配下就OK 了

------解决思路----------------------

引用:

Quote: 引用:

Quote: 引用:

TO ky_min
一定需要路径来说明还是用标明父类是哪个的列，对于表设计我不太懂，如果没有路径或者父类说明，关系表就感觉特别繁琐，在这个表设计上，我应该怎样修改，会有利一些？

感谢！

如果你只有这个贴的这个特殊处理，加个路径就很方便了，甚至连层级和类型都不用，不过 层级和类型 你应该是有用在其它地方的~~
 考虑以后可能会有其它报表或树功能要求的节点列表，这种类似树的表设计，一般要加上路径、所属父类、层级，虽然有一定冗余，但是效率会提高N多

 是的，诚如您所说，今后还会用到其他地方，例如菜单等。

所属父类用关系表表示关系已经存在了一次，如果再在课程表里面表达一次却是冗余，我想加个路径倒是很好的想法，只是，加路径的话，对于4、5、6或者更多地子关系的隶属还是要SQL来处理一下，我再考虑通过存储过程的方式来实现是否更好，表结构现在没法动了。

如果表结构不能动，那确实麻烦了，你考虑建个函数，如#24的方式，也可以用CTE 递归出一个路径，不效率应该都不会有一个现成的路径高

;WITH CTE AS(
	SELECT ID,[名称],[类型],[层级],[所属父ID],CAST([ID]AS VARCHAR(8000)) AS [PATH] FROM [课程表] WHERE [所属父ID]=0
	UNION ALL
	SELECT A.ID,A.[名称],A.[类型],A.[层级],A.[所属父ID],B.[PATH]+'-'+CAST(A.[ID]AS VARCHAR(8000)) FROM [课程表] A INNER JOIN CTE B ON A.[所属父ID]=B.ID
)
--SELECT * FROM CTE
SELECT
	A.[名称] [第二类]
	,E.[名称] [第三类]
	,D.[名称] [学员]
FROM
	[课程表] A
	LEFT JOIN CTE B ON A.[类型]=B.[类型]AND A.[层级]<B.[层级]
	INNER JOIN CTE E ON B.[PATH]LIKE E.[PATH]+'%'AND E.[层级]=3
	INNER JOIN [关系表] C ON C.[父ID]=B.[ID]AND C.[关系类型]='学员'
	INNER JOIN [学员表] D ON C.[成员ID]=D.[ID]
WHERE
	A.ID=2
GROUP BY
	A.[名称]
	,E.[名称]
	,D.[名称]
ORDER BY
	[第二类]
	,[第三类]

如果表可以动，可以加个路径，可以用SQL批量更新，对4，5，6的处理也很方便