分层SQL查询

发布于 2024-11-07 04:31:01 字数 352 浏览 0 评论 0原文

我使用的是 SQL Server 2005，有一个名为 Docs 的表，其中有一个名为 Path 的列。

Path 列包含文件路径：

"C:\MyDocs\1.txt"
"C:\MyDocs\Folder1\3.txt"

我需要查询在单个查询中检索每个文件夹和子文件夹有多少个子文件夹。

在上面的示例中，我需要查询来检索：

“MyDocs”的 3 个子项（1 个文件夹和 2 个文件） “Folder1”有 1 个子项（1 个文件）。

有什么想法吗？

谢谢。

原文

I am using SQL Server 2005, I have a single table called Docs with a single column called Path.

Path column contains file paths:

"C:\MyDocs\1.txt"
"C:\MyDocs\Folder1\3.txt"

I need my query to retrieve in a single query how many children each folder and subfolder has.

In my example above I need the query to retrieve:

3 children (1 folder and 2 files) for "MyDocs"
1 child (1 file) for "Folder1".

Any ideas?

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

哭泣的笑容 2024-11-14 04:31:01

添加带有目录路径的列 dir（例如，对于“C:\MyDocs\Folder1\3.txt”，它应包含“C:\MyDocs\Folder1\”）。然后：

select dirs.path, count(*) from Docs files 
join (select distinct dir as path from Docs) dirs
    on (files.dir like dirs.path + '%')
group by dirs.path

我没有运行查询，但想法应该很清楚。

Add a column dir with directory path (ex. for "C:\MyDocs\Folder1\3.txt" it should contain "C:\MyDocs\Folder1\"). Then:

select dirs.path, count(*) from Docs files 
join (select distinct dir as path from Docs) dirs
    on (files.dir like dirs.path + '%')
group by dirs.path

I didn't run the query, but the idea should be clear.

回复收藏 0 原文

残疾 2024-11-14 04:31:01

您需要将路径拆分为单独的字符串并对数据进行标准化。这是关于在 sql 中分割字符串的上一个问题。

完成后，您可以使用递归 CTE 来获取数据。

回复收藏 0 原文

对你的占有欲 2024-11-14 04:31:01

您可以使用字符串操作来解决此问题，为了清楚起见，可以使用两个 CTE：

WITH
R1(RevPath) as (select reverse(Path) from Docs),
R2(ParentFolder) as
(select reverse( substring(RevPath, charindex('\', RevPath), 3333)) from R1)
select ParentFolder, count(*) from R2
group by ParentFolder
go

您可能需要根据您的数据对此进行一些调整。如果您的表仅列出文件，则此查询应该没问题。如果它还列出了带有尾部反斜杠的文件夹，请从计数中减去 1。

如何获取其中包含的所有文件夹和文件的列表

WITH
R1(Path, RevPath) as (select Path, reverse(Path) from Docs),
R2(ParentFolder, Path) as
(select
  reverse( substring(RevPath, charindex('\', RevPath), 3333)),
  Path
 from R1)
select * from R2
where ParentFolder LIKE 'C:\some\folder\%' -- all folders below C:\some\folder
go

未测试！

You can solve this using string manipulation and, for clarity, two CTEs:

WITH
R1(RevPath) as (select reverse(Path) from Docs),
R2(ParentFolder) as
(select reverse( substring(RevPath, charindex('\', RevPath), 3333)) from R1)
select ParentFolder, count(*) from R2
group by ParentFolder
go

You might have to tweak this a little bit depending on your data. If your table lists only files, this query should be fine. If it lists also folders with a trailing backslash, subtract 1 from the count.

How to get a listing of all folders and files contained in them

WITH
R1(Path, RevPath) as (select Path, reverse(Path) from Docs),
R2(ParentFolder, Path) as
(select
  reverse( substring(RevPath, charindex('\', RevPath), 3333)),
  Path
 from R1)
select * from R2
where ParentFolder LIKE 'C:\some\folder\%' -- all folders below C:\some\folder
go

Not tested!

回复收藏 0 原文

~没有更多了~