本文介绍了一种使用 SQL 递归公用表达式 (Recursive CTE) 查询多级目录结构的方法，并展示了一个具体的例子来说明如何统计特定目录下所有子目录中的文档数量。
WITH RecursiveCategories (parent_dir_id,dir_id ,dir_name , Level)
AS
(
SELECT parent_dir_id, dir_id, dir_name, 0 AS Level
FROM edms_directory
WHERE dir_id = 0
UNION ALL
SELECT edms_directory.parent_dir_id, edms_directory.dir_id, edms_directory.dir_name,
Level + 1
FROM edms_directory
INNER JOIN RecursiveCategories AS d
ON edms_directory.parent_dir_id = d.dir_id
)
select count(dir_id) from edms_document where dir_id in (
Select dir_id From RecursiveCategories
) and is_deleted <> 'Y' and is_link <> 'Y'
AS
(
SELECT parent_dir_id, dir_id, dir_name, 0 AS Level
FROM edms_directory
WHERE dir_id = 0
UNION ALL
SELECT edms_directory.parent_dir_id, edms_directory.dir_id, edms_directory.dir_name,
Level + 1
FROM edms_directory
INNER JOIN RecursiveCategories AS d
ON edms_directory.parent_dir_id = d.dir_id
)
select count(dir_id) from edms_document where dir_id in (
Select dir_id From RecursiveCategories
) and is_deleted <> 'Y' and is_link <> 'Y'
扫一扫
742
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
