请考虑下表。他们描述了学校的层次结构和每个学生的笔记。
users
-------------------------------------------------------------
root_id obj_id obj_type obj_ref_id obj_role
-------------------------------------------------------------
1 2 student 7 learn
1 3 student 7 learn
1 1 principal 1 lead
1 4 mentor 1 train teachers
1 5 trainee 4 learn teaching
1 6 trainee 4 learn teaching
1 7 teacher 1 teach
2 8 student 9 learn
2 9 principal 9 lead
notes
--------------------------------------------------------------
note_id obj_id note
--------------------------------------------------------------
1 2 foo
2 2 bar
3 2 baz
4 3 lorem
5 8 ipsum
我需要写出每个用户的层次结构和笔记数量，如下所示：
-------------------------------------------------------------------------------------------
obj_id notes obj_path
-------------------------------------------------------------------------------------------
1 0 principal 1 (lead)
2 3 student 2 (learn) > teacher 7 (teach) > principal 1 (lead)
3 1 student 3 (learn) > teacher 7 (teach) > principal 1 (lead)
4 0 mentor 4 (train teachers) > principal 1 (lead)
5 0 trainee 5 (learn teaching) > mentor 4 (train teachers) > principal 1 (lead)
6 0 trainee 6 (learn teaching) > mentor 4 (train teachers) > principal 1 (lead)
7 0 teacher 7 (teach) > principal 1 (lead)
8 1 student 8 (learn) > principal 2 (lead)
9 0 principal 9 (lead)
为此，我知道我需要使用如下循环：
declare cur cursor for
select obj_id from users order by root_id
open cur
declare @obj_id int
fetch next from cur into @id
while (@@FETCH_STATUS = 0)
begin
select obj_role from users where obj_id = @obj_id
fetch next from cur into @obj_id
end
close cur
deallocate cur
这就是我到目前为止所拥有的，但我不明白如何从这里开始。有人可以在路上帮助我吗？
了解使用游标将逐个处理每个单独的记录。
递归 CTE 将是一个更好的解决方案：
- SQL 服务器 CTE 和递归示例
- 用于获取树层次结构的 CTE 递归
- 递归 CTE 如何逐行运行？
像这样：
DECLARE @User TABLE
(
[root_id] INT
, [obj_id] INT
, [Obj_type] NVARCHAR(100)
, [obj_ref_id] INT
, [obj_role] NVARCHAR(100)
);
DECLARE @Notes TABLE
(
[note_id] INT
, [obj_id] INT
, [note] NVARCHAR(255)
);
INSERT INTO @Notes (
[note_id]
, [obj_id]
, [note]
)
VALUES ( 1, 2, 'foo ' )
, ( 2, 2, 'bar ' )
, ( 3, 2, 'baz ' )
, ( 4, 3, 'lorem' )
, ( 5, 8, 'ipsum' );
INSERT INTO @User (
[root_id]
, [obj_id]
, [Obj_type]
, [obj_ref_id]
, [obj_role]
)
VALUES ( 1, 2, 'student', 7, 'learn' )
, ( 1, 3, 'student', 7, 'learn' )
, ( 1, 1, 'principal', 1, 'lead' )
, ( 1, 4, 'mentor', 1, 'train teachers' )
, ( 1, 5, 'trainee', 4, 'learn teaching' )
, ( 1, 6, 'trainee', 4, 'learn teaching' )
, ( 1, 7, 'teacher', 1, 'teach' )
, ( 2, 8, 'student', 9, 'learn' )
, ( 2, 9, 'principal', 9, 'lead' );
WITH [Hierarchy]
AS ( SELECT [obj_id] AS [root_obj]
, [obj_ref_id] AS [root_obj_ref]
, [obj_id]
, [obj_ref_id]
, CONVERT(
NVARCHAR(MAX)
, [Obj_type] + ' ' + CONVERT(NVARCHAR, [obj_id]) + ' ('
+ [obj_role] + ')'
) AS [obj_path]
FROM @User
UNION ALL
SELECT [a].[root_obj]
, [a].[root_obj_ref]
, [b].[obj_id]
, [b].[obj_ref_id]
, [a].[obj_path] + ' > ' + [b].[Obj_type]
+ CONVERT(NVARCHAR, [b].[obj_id]) + ' (' + [b].[obj_role] + ')' AS [obj_path]
FROM [Hierarchy] [a]
INNER JOIN @User [b]
ON [b].[obj_id] = [a].[obj_ref_id]
WHERE [a].[obj_id] <> [a].[obj_ref_id] ) --Here, basically continue the recursion while the record isn't referencing itself. The final will include that self referencing record.
SELECT [Hierarchy].[root_obj] AS [obj_id]
, (
SELECT COUNT(*)
FROM @Notes
WHERE [obj_id] = [Hierarchy].[root_obj]
) AS [notes] --Here we'll go out and get the count of notes.
, [Hierarchy].[obj_path]
FROM [Hierarchy]
WHERE [Hierarchy].[obj_id] = [Hierarchy].[obj_ref_id] --Then we only went those records built up to the final record that was referencing itself.
ORDER BY [Hierarchy].[root_obj];