关系数据库中的分层过滤
我的程序中有一堆项目，它们都属于特定类别。我只想退回属于该类别的商品。问题是类别可以有父类别。例如，假设有一个类别“Stuff”，其子类别“Food”和子类别“Fruit”。我有这些物品：苹果、梨、巧克力和电脑。
如果我想显示所有水果，很容易使用“WHERE item.category = FRUIT_ID”子句进行数据库查询。然而，如果我希望所有食物都包含在内，我还需要一种将水果放入其中的方法。
我知道某些数据库（如 Oracle）有递归查询的概念，这可能是正确的解决方案，但我在分层数据方面没有很多经验，正在寻找一般建议。假设我对数据库模式有无限的控制权，类别树最多可能只有 5 个类别深度，而且我需要它尽可能快。
I have a bunch of items in my program that all belong to a specific category. I'd like to return only the items that belong to that category. The problem is that categories can have parent categories. For example, let's say there's a category "Stuff" with the child category "Food" with the child category "Fruit". I have the items, Apple, Pear, Chocolate, and Computer.
If I want to display all of the fruits, it's easy to do a database query with a "WHERE item.category = FRUIT_ID" clause. However, if I want all foods to be included, I need a way to get the fruits in there, too.
I know that some databases, like Oracle, have a notion of recursive queries, and that might be the right solution, but I don't have a lot of experiences with hierarchical data and am looking for general suggestions. Assume I have unlimited control over the database schema, the category tree only goes maybe 5 categories deep maximum, and I need it to be as ridiculously fast as possible.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
看看 邻接列表模型 - 它并不完美（更新非常慢），但在某些情况下（分层查询），它是一个很好的表示，特别是对于像您这样的问题。
Have a look at the adjacency list model - it's not perfect (it's very slow to update), but in some situations (hierarchical queries), it's a great representation, especially for problems like yours.
有 整本书充满了用 SQL 表示树的设计策略。仅仅为了纯粹的聪明点就值得一看。
There's a whole book full of design strategies for representing trees in SQL. It's worth looking at just for the sheer clever points.
假设您的类别树足够小，可以缓存，那么您最好将类别树保留在内存中，并在该树上有一个函数，该函数将生成低于给定类别的类别 id 列表。
然后，当您查询数据库时，只需使用带有子 ID 列表的
IN子句Assuming your category tree is small enough to be cached, you might be better off keeping the category tree in memory and have a function over that tree that will generate a list of category id's that are below a given category.
Then when you query the database, you just use an
INclause with the list of child IDs一种可能的解决方案是将层次结构与实际分类分开。例如，苹果既可以被归类为水果，也可以被归类为食物。该分类不知道水果是一种食物，但您可以在其他地方定义它。然后，您的查询将像
where Category='food'一样简单。或者，您可以在构建查询之前遍历层次结构，它需要类似
wherecategory='food' 或category='fruit'的内容。One possible solution is to separate the hierarchy from the actual categorization. For instance, an apple could be categorized as both a fruit and a food. The categorization has no knowledge that a fruit is a food, but you could define that somewhere else. Then, your query would be as simple as
where category='food'.Alternatively, you could go through the hierarchy before building your query and it would require something like
where category='food' or category='fruit'.我认为您的数据库模式非常好，但是此搜索的实现实际上取决于您特定的 RDBMS。他们中的很多人都有方法来执行这种递归。我能想到的一个例子是 SQL Server 对 公用表表达式 的支持这是那些令人讨厌的光标的快速替代品。
如果您指定您正在使用哪个 RDBMS，您可能会得到更具体的答案。
I think your database schema is quite fine, but the implementation of this search really depends on your specific RDBMS. A lot of them have ways to perform this sort of recursion. One example I can think of is SQL Server's support of Common Table Expressions which are lightning fast alternatives to those nasty cursors.
If you specify which RDBMS you're using, you might get more specific answers.