SQL递归行转列

[kevinqc1989]

我有三张表如下，分别记录类别的树结构、类别信息、类别分类名称：
categorytree：id , parentId , categoryID
category：categoryID，categoryName , level
categorylevel : level , catedorylevelName

外键应该可以看出来吧。
假设表内数据：
categorytree: id parentId categoryID
1 0 1
2 0 2
3 1 3
4 3 4
5 2 7
category: categoryID categoryName level
1 企划 1
2 开发 1
3 年度计划 2
4 项目A 3
7 模块开发 2
categorylevel: level categorylevelName
1 分类一
2 分类二
3 分类三

表结构是为了实现分类的可扩展性和树枝同级的项目名可能重用的考虑，
例如增加到分类四，或年度计划既出现在开发的子项也出现在企划的子项。
父节点ID为零的就是根节点

现在我想根据某个叶子节点，查找出到根节点的路径，然后按分类名作为列值查找出来
例如：查找ID = 4和5 的叶子节点
列：分类一 分类二 分类三
企划 年度计划 项目A
开发 模块开发

列根据categorylevel表确定有几列 叶子节点不一定为第几级别，没有就为空

这样通过sql嵌套查询可以实现吗，我用的数据库是mysql，如何实现呢
求助各位大神啊！！

[--小F--]

MYSQL的递归实在是太纠结了。去找个例子看吧。要写死人的

[-Tracy-McGrady-]

反正我不会，顶不顶是一种态度。

[kevinqc1989]

求教版主，递归和行转列的例子都能找到，但是怎么结合起来呢，没有想通...因为每一条递归查询都是几行，又希望把他们能转换成列变成一行...或者怎么设计数据库表结构可以既满足可扩展性又可以不用这么复杂的查询呢？ 版主大神请赐教啊！

[chen357313771]

IF OBJECT_ID('tempdb..#Categorytree','U') IS NOT NULL DROP TABLE #Categorytree
CREATE TABLE #Categorytree
(
	ID			 INT
	,ParentID	 INT
	,CategoryID	 INT
)

INSERT INTO #Categorytree
SELECT 1, 0, 1 UNION ALL 
SELECT 2, 0, 2 UNION ALL
SELECT 3, 1, 3 UNION ALL
SELECT 4, 3, 4 UNION ALL
SELECT 5, 2, 7

IF OBJECT_ID('tempdb..#Categorytree','U') IS NOT NULL DROP TABLE #Category
CREATE TABLE #Category
(
	CategoryID		INT
	,CategoryName	NVARCHAR(20)
	,LevelID		INT
)

INSERT INTO #Category
SELECT 1, N'企划', 1 UNION ALL
SELECT 2, N'开发', 1 UNION ALL
SELECT 3, N'年度计划', 2 UNION ALL
SELECT 4, N'项目A', 3 UNION ALL
SELECT 7, N'模块开发', 2 

IF OBJECT_ID('tempdb..#Categorytree','U') IS NOT NULL DROP TABLE #Categorylevel
CREATE TABLE #Categorylevel
(
	LevelID				INT
	,CategorylevelName	NVARCHAR(20)
)


INSERT INTO #Categorylevel
SELECT 1,N'分类一' UNION ALL
SELECT 2,N'分类二' UNION ALL
SELECT 3,N'分类三'

DECLARE @ID INT =4

;WITH CTE(ID)
AS (
	SELECT CategoryID 
	FROM #Categorytree
	WHERE ID=@ID
	UNION ALL 
	SELECT ParentID
	FROM CTE AS A
	JOIN #Categorytree AS B ON A.ID=B.CategoryID
	WHERE ParentID>0
)
SELECT *
FROM (
SELECT C.CategorylevelName,B.CategoryName
FROM CTE AS A
JOIN #Category AS B ON A.ID=B.CategoryID
JOIN #Categorylevel AS C ON B.LevelID=C.LevelID
) T 
PIVOT(MAX(CategoryName) FOR CategorylevelName IN([分类一],[分类二],[分类三])) p

没看懂。。瞎写的。。

[排山倒海呦]

sqlserver 里的写法

declare @categorytree table(id int ,parentId int, categoryID int)
insert into @categorytree select 
1,0,1 union all select 
2,0,2 union all select 
3,1,3 union all select 
4,3,4 union all select 
5,2,7 
declare @category table (categoryID int, categoryName varchar(10), level int)
insert into @category select 
1 ,'企划', 1 union all select 
2 ,'开发', 1 union all select 
3 ,'年度计划', 2 union all select 
4 ,'项目A', 3 union all select 
7 ,'模块开发', 2
declare @categorylevel table (level int ,categorylevelName varchar(10))
insert into @categorylevel select 
1 ,'分类一' union all select 
2 ,'分类二' union all select 
3 ,'分类三'
;with cte as
(select id  ,parentId , categoryID ,level=1 from @categorytree
union all
select c.id ,t.parentId,c.categoryID,level=c.level+1 
from @categorytree t inner join cte c
on t.id=c.parentID
)
,cte2 as (select a.*,categoryname=isnull(c.categoryname,b.categoryname),d.categorylevelname from cte a left join @category b 
on a.categoryID=b.categoryID
left join @category c on a.parentID = c.categoryID
left join @categorylevel d on a.level=d.level
)
select id,[分类一],[分类二],[分类三] 
from (select id,categoryname,categorylevelname from cte2 ) src
pivot (max(categoryname) for categorylevelname in ([分类一],[分类二],[分类三]))as p

[排山倒海呦]

id 分类一 分类二 分类三 1 企划 NULL NULL 2 开发 NULL NULL 3 企划 年度计划 NULL 4 年度计划 企划 项目A 5 开发 模块开发 NULL