在sql server中的树结构中查找子树下的所有叶节点 [英] Find all the leaf nodes below a subtree in a Tree structure in sql server
问题描述
我有一个树结构，以及它在 sql server 数据库中的客户类别的后续分配表.
I've a tree structure, and its subsequent assignment table for customer categories in an sql server database.
CustomerCategory (CategoryID, ParentId)
CustomerInCategory(CustomerID, CategoryID)
如果一个 CustomerCategory 有任何客户分配给它，我们不能向它添加另一个子类别.因此，Customer 只能添加到每个子树中的最低级别.换句话说，这个查询的结果
If a CustomerCategory has any customer assigned to it, we can't add another subcategory to it. So, Customer can only be added to the lowest level in every sub tree. In other sense, the result of this query
SELECT * FROM `CustomerCategory` WHERE `CategoryId` NOT IN
(SELECT DISTINCT `parentid` FROM `CustomerCategory` WHERE `parentid` IS NOT NULL)
将产生叶节点.另一件事是，这棵树可能有不同级别的子树，我们也不想限制级别的数量，但是，我们的用户不会需要超过 10 个级别.将此视为说明
would yield leaf nodes. The Other thing is that, this tree might have subtrees of different levels, and we also, don't want to bound the number of levels in anyway, however, our users won't need more than 10 levels. Consider this as an illustration
CategoryID------ParentID---------------Name
1 NULL All Customers
2 1 Domestic
3 1 International
4 2 Independent Retailers
5 2 Chain Retailers
6 2 Whole Sellers
7 5 A-Mart
8 5 B-Mart
9 4 Grocery Stores
10 4 Restaurants
11 4 Cafes
CustomerID---------CustomerName----------Category
1 Int.Customer#1 3
2 Int.Customer#2 3
3 A-Mart.Branch#1 7
4 A-Mart.Branch#2 7
5 B-Mart.Branch#1 8
6 B-Mart.Branch#2 8
7 Grocery#1 9
8 Grocery#2 9
9 Grocery#3 9
10 Restaurant#1 10
11 Restaurant#2 10
12 Cafe#1 11
13 Wholeseller#1 6
14 Wholeseller#2 6
我的要求是这样的，给定类别中的一个节点，返回附加到其下任何节点的所有客户".
My requirement is something like this, "Given a node in Categories, Return All the Customers attached to any node below it".
我怎样才能用 sql 做到这一点?
How can I do it with sql?
显然这可以通过代码中的递归调用来完成，但是我们如何在 t-sql 中做到这一点(无需多次调用存储过程或使用基于文本的搜索)?
Obviously this can be done with a recursive call in the code, but how can we do it in t-sql (without calling a stored procedure several times or using text-based search)?
任何机构都可以使用 CTE 来解决这个问题吗?
Can any body, Use a CTE to solve this problem?
我有一个类似这样的结果集
I have a result set of something like this in mind
CustomerID--------Customer Name----------------CategoryId----------CAtegoryName
12 Cafe#1 11 Cafes
12 Cafe#1 4 IndependentRetailers
12 Cafe#1 2 Demoestic
12 Cafe#1 1 AllCustomers
.
.
.
4 A-Mart.Branch#2 7 A-Mart
4 A-Mart.Branch#2 5 Chain Retailers
4 A-Mart.Branch#2 2 Domestic
4 A-Mart.Branch#2 1 All Customers
.
.
.
14 Wholeseller#2 6 WholeSellers
14 Wholeseller#2 2 Domestic
14 Wholeseller#2 1 All Customers
布局这样的结果不一定是一个好主意，这会消耗太多空间，可能不需要的东西，但是，在这样的结果集中搜索会非常快.如果我想找到下面的所有客户说 categoryId = 2 ，我会简单地查询
This is not necessarily a good Idea to layout a result like this, This would consume too much space, something that might not be required, yet, a search in such result set would be very fast. If I want to find all the customers below say categoryId = 2 , I would simply query
SELECT * FROM resultset where category ID = 2
非常欢迎任何改进数据模型的建议！如果它有助于解决这个问题.
Any suggestions to improve the data model is super welcomed! If It helps solving this problem.
再一次，我并不关注这个结果集.任何其他解决问题的建议，给定类别中的一个节点，返回附加到其下任何节点的所有客户"，这是公认的.
Once again, I'm not fixated on this result set. Any other Suggestion that solves the problem, "Given a node in Categories, Return All the Customers attached to any node below it", is well accepted.
推荐答案
您可以使用 CTE 递归构建包含所有父子关系的表，并使用 where 子句仅获取您需要的子树(在我的示例中), 一切都在 CategoryId 下 5) :
You can use a CTE to recursively build a table containing all the parent-child relationships and use the where clause to get only the subtree you need (in my example, everyting under CategoryId 5) :
WITH CategorySubTree AS (
SELECT cc.CategoryId as SubTreeRoot,
cc.CategoryId
FROM CustomerCategory cc
UNION ALL
SELECT cst.SubTreeRoot, cc.CategoryId
FROM CustomerCategory cc
INNER JOIN CategorySubTree cst ON cst.CategoryId = cc.parentId
)
SELECT cst.CategoryId
FROM CategorySubTree cst
WHERE cst.SubTreeRoot = 5
您可以修改此查询以添加您需要的任何内容，例如，将客户链接到子树中的类别节点:
You can modify this query to add whatever you need, for example, to get customers linked to the category nodes in the subtree :
WITH CategorySubTree AS (
SELECT cc.CategoryId as SubTreeRoot,
cc.CategoryId
FROM CustomerCategory cc
UNION ALL
SELECT cst.SubTreeRoot, cc.CategoryId
FROM CustomerCategory cc
INNER JOIN CategorySubTree cst ON cst.CategoryId = cc.parentId
)
SELECT cst.CategoryId,cic.CustomerId
FROM CategorySubTree cst
INNER JOIN CustomerInCategory cic ON cic.CategoryId = cst.CategoryId
WHERE cst.SubTreeRoot = 5
当然，您可以加入更多表格以获取标签和其他所需信息.
And of course you can join further tables to get labels and other needed information.
这篇关于在sql server中的树结构中查找子树下的所有叶节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
