[求教]疑难SQL语句求解

Question

没看懂。父ID是课程ID?子ID 是学员ID吗？

Answer 1

你还有一些需求不明的地方，我先贴一个，看下是不是你要的结果
SELECT
A.[名称] [第二类]
,B.[名称] [第三类]
,D.[名称] [学员]
FROM
[课程表] A
LEFT JOIN [课程表] B ON A.[类型]=B.[类型]AND A.[层级]<B.[层级]
INNER JOIN [关系表] C ON C.[父ID]=B.[ID]
LEFT JOIN [学员表] D ON C.[成员ID]=D.[ID]
WHERE
A.ID=2
ORDER BY
A.[名称]
,B.[名称]

Answer 2

with course as 
(select 1 as id,'教学计划'as name,'JH'as type,1 as level union all
select 2,'课程' ,'KC',2 union all
select 3,'制图设计课程', 'KC',3 union all
select 4,'程序设计课程', 'KC', 3 union all
select 5,'HTML课程','KC', 3 union all
select 6,'CSS课程','KC',3 union all
select 7,'培训','PX',2 union all
select 8,'礼仪培训','PX',3 union all
select 9,'消防培训','PX', 3 union all
select 10,'反恐培训','PX', 3 union all
select 11,'各类灭火器使用培训', 'PX',4 union all
select 12,'HTML编写规范课程','KC',4 union all
select 13,'JS课程', 'KC',4 union all
select 14,'JQUERY课程','KC',4 union all
select 15,'接待礼仪','PX',4 ),
student as 
(select 1 as id, '刘德华'  as name union all
select 2,'张学友'union all
select 3,'梁朝伟'union all
select 4,'郭富城'union all
select 5,'刘青云'union all
select 6,'黎明'union all
select 7,'张曼玉'union all
select 8,'王祖贤'union all
select 9,'容祖儿'union all
select 10,'任贤齐')

Answer 3

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Test', FILENAME = N'C:\DATA\Test.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Test_log', FILENAME = N'C:\DATA\Test_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GOCREATE TABLE [dbo].[课程表](
[ID] [int] IDENTITY(1,1) NOT NULL,
[名称] [nchar](10) NULL,
[类型] [nchar](10) NULL,
[层级] [nchar](10) NULL,
 CONSTRAINT [PK_group] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOCREATE TABLE [dbo].[学员表](
[ID] [int] IDENTITY(1,1) NOT NULL,
[名称] [nchar](10) NULL,
 CONSTRAINT [PK_学员表] PRIMARY KEY CLUSTERED 
(
[ID] 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
CREATE TABLE [dbo].[关系表](
[父ID] [int] NULL,
[子ID] [int] NULL,
[关系类型] [nchar](10) NULL
) ON [PRIMARY]GO
insert into[课程表] values('教学计划','JH',1)
insert into[课程表] values('课程','KC',2)
insert into[课程表] values('制图设计课程','KC',3)
insert into[课程表] values('程序设计课程','KC',3)
insert into[课程表] values('HTML课程','KC',3)
insert into[课程表] values('CSS课程','KC',3)
insert into[课程表] values('培训','PX',2)
insert into[课程表] values('礼仪培训','PX',3)
insert into[课程表] values('消防培训','PX',3)
insert into[课程表] values('反恐培训','PX',3)
insert into[课程表] values('各类灭火器使用培训','PX',4)
insert into[课程表] values('HTML编写规范课程','KC',4)
insert into[课程表] values('JS课程','KC',4)
insert into[课程表] values('JQUERY课程','KC',4)
insert into[课程表] values('接待礼仪','PX',4)insert into [学员表] values('刘德华')
insert into [学员表] values('张学友')
insert into [学员表] values('梁朝伟')
insert into [学员表] values('郭富城')
insert into [学员表] values('刘青云')
insert into [学员表] values('黎明')
insert into [学员表] values('张曼玉')
insert into [学员表] values('王祖贤')
insert into [学员表] values('容祖儿')
insert into [学员表] values('任贤齐')insert into [关系表] values(1,2,'教学')
insert into [关系表] values(1,7,'教学')
insert into [关系表] values(2,3,'教学')
insert into [关系表] values(2,4,'教学')
insert into [关系表] values(2,5,'教学')
insert into [关系表] values(2,6,'教学')
insert into [关系表] values(7,8,'教学')
insert into [关系表] values(7,9,'教学')
insert into [关系表] values(7,10,'教学')
insert into [关系表] values(7,11,'教学')
insert into [关系表] values(5,12,'教学')
insert into [关系表] values(4,13,'教学')
insert into [关系表] values(4,14,'教学')
insert into [关系表] values(9,11,'教学')
insert into [关系表] values(8,15,'教学')
insert into [关系表] values(3,1,'学员')
insert into [关系表] values(4,2,'学员')
insert into [关系表] values(5,3,'学员')
insert into [关系表] values(6,4,'学员')
insert into [关系表] values(8,5,'学员')
insert into [关系表] values(9,6,'学员')
insert into [关系表] values(10,7,'学员')
insert into [关系表] values(11,8,'学员')
insert into [关系表] values(12,9,'学员')
insert into [关系表] values(13,10,'学员')
insert into [关系表] values(14,7,'学员')
insert into [关系表] values(15,8,'学员')SELECT
    A.[名称] [第二类]
    ,B.[名称] [第三类]
    ,D.[名称] [学员]
FROM
    [课程表] A
    LEFT JOIN [课程表] B ON A.[类型]=B.[类型]AND A.[层级]<B.[层级]
    INNER JOIN [关系表] C ON C.[父ID]=B.[ID]
    LEFT JOIN [学员表] D ON C.[子ID]=D.[ID]
WHERE
    A.ID=2
ORDER BY
    A.[名称]
    ,B.[名称]
查询结果如下：

Answer 4

TO：ky_min
如果有更下层的，汇总到第三层，意思就是它隶属于那个大类，无论子层有多深，都汇集到第三层显示

Answer 5

TO：ky_min
例如，刘德华属于的HTML课程学员，突然这个HTML增加了一个子课程 ：HTML5（第四层了），刘德华又参加了这个课程，那么，他显示还是在第三层（即HTML课程），第四层就不显示。这个时候，HTML5下面又增加了一个子课程：HTML6（第六层了），任贤齐参加了这个课程的学习，那么在第三层的HTML课程里，就查询到了任贤齐，并且显示在第三层下面

Answer 6

你假设的例子中 HTML  和  HTML5  的父子关系  从哪个数据上体现呢？
例如
名称   类型   层级
HTML  KC   3
HTML5   KC  4
从上面看不出他们的父子关系
ID是自增的这边就不显示了

Answer 7

在关系表里面存储他们的父子关系。只是，现在还没有增加这一笔数据，一旦增加，就会在关系表里增加一笔记录，记录着HTML5隶属于HTML课程

Answer 8

我假设你的路径是这样的
ID 名称 类型 层级 路径
1 课程 KC 1 -1-
2 课程1 KC 2 -1--2-
3 课程2 KC 3 -1--2--3-
4 课程3 KC 3 -1--2--4-
5 课程4 KC 3 -1--2--5-
6 课程5 KC 4 -1--2--3--6-
如果不是，也应该是  一列标明父类是哪个的列，不过路径效率会高些，一般表设计时， 路径 和所属父类 两列都有
SELECT
A.[名称] [第二类]
,E.[名称] [第三类]
,D.[名称] [学员]
FROM
[课程表] A
LEFT JOIN [课程表] B ON A.[类型]=B.[类型]AND A.[层级]<B.[层级]
INNER JOIN [课程表] E ON B.[路径]LIKE E.[路径]+'%'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
[第二类]
,[第三类]

Answer 9

表结构设计问题。关系表里面学员关系，课程关系2个用途 放在一个表里面，元数据
insert into [关系表] values(7,11,'教学') 这有问题。  他们父ID是第二层的。子ID是第四层。

Answer 10

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

Answer 11

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

Answer 12

写个函数，输入课程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 了

Answer 13

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

Answer 14

 是的，诚如您所说，今后还会用到其他地方，例如菜单等。所属父类用关系表表示关系已经存在了一次，如果再在课程表里面表达一次却是冗余，我想加个路径倒是很好的想法，只是，加路径的话，对于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的处理也很方便

Answer 15

 是的，诚如您所说，今后还会用到其他地方，例如菜单等。所属父类用关系表表示关系已经存在了一次，如果再在课程表里面表达一次却是冗余，我想加个路径倒是很好的想法，只是，加路径的话，对于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的处理也很方便路径存储怎么存储？用啥格式？ 3-2-1 这样吗 感觉SQL 处理起来也比较麻烦。

Answer 16

表结构设计问题。关系表里面学员关系，课程关系2个用途 放在一个表里面，元数据
insert into [关系表] values(7,11,'教学') 这有问题。  他们父ID是第二层的。子ID是第四层。
这个冲突是这样的，表结构设计时考虑到了，在他们当时有个案子也是设计两三个关系表存储不同的关系，后来发现用一个表存储关系更加精简，只要在关系类型里面注明所属关系类型，查询的时候根据这个来查询，例如：有个菜单，也可以把菜单的父子关系存储在这个表里面，只要关系类型表达清楚就可以了。但是现在轮到我来写这个SQL，我就发现水平差太多求教各位了，谢谢大家帮忙，我研究一下楼上各位给我的思路！

Answer 17

没感觉到好啊，分开存储，每次用表之间一个聚集扫描，你现在不分开还是一个聚集扫描。（这个表大 消耗更大）表结构设计问题。关系表里面学员关系，课程关系2个用途 放在一个表里面，元数据
insert into [关系表] values(7,11,'教学') 这有问题。  他们父ID是第二层的。子ID是第四层。

这个冲突是这样的，表结构设计时考虑到了，在他们当时有个案子也是设计两三个关系表存储不同的关系，后来发现用一个表存储关系更加精简，只要在关系类型里面注明所属关系类型，查询的时候根据这个来查询，例如：有个菜单，也可以把菜单的父子关系存储在这个表里面，只要关系类型表达清楚就可以了。但是现在轮到我来写这个SQL，我就发现水平差太多求教各位了，谢谢大家帮忙，我研究一下楼上各位给我的思路！

Answer 18

To: prowan                 还有些小问题,在处理