自引用表和递归 SQL 函数
我有一个类别表，其中包含：
- categoryIDparentCategoryIDcategoryName
- 正在
- MySQL
和一个项目表，其中包含：
- 我
- 使用
- itemIDcategoryIDitemName
。我想编写一个查询，该查询将返回给定类别 ID 的类别中的项目计数。查询应返回给定类别的所有子类别中的所有项目的总数。
我希望这是有道理的..抱歉，如果我没有使用正确的术语。
I have a category table with:
- categoryID
- parentCategoryID
- categoryName
and an items table with:
- itemID
- categoryID
- itemName
I am using MySQL. i want to write a query that will return a count of Items in a category given a categoryID. The query should return total count for all items in all subcategories of the given category.
I hope this makes sense.. sorry if i am not using the correct nomenclature.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你对这个模式有多困惑？它被称为“邻接表”，从概念上讲它很简单，但它有一些真正的缺点。其中最重要的是无法查询所有后代。
看一下这个，并考虑表示树的替代方法是否更适合您：
http ://pugs.postgresql.org/files/ModelingTrees.pdf
How stuck are you on that schema? It's called an "Adjacency List", and it's simple enough, conceptually, but it has some real shortcomings. The most significant of which is the inability to query for all descendants.
Take a look at this, and consider whether an alternative method of representing trees might work better for you:
http://pugs.postgresql.org/files/ModelingTrees.pdf
当然这是可能的，但效率不是很高。
你应该使用嵌套集合结构：
http://intelligent-enterprise.informationweek.com/001020/celko1_1。 jhtml;jsessionid=AFUXE0ZF4PTNXQE1GHPSKH4ATMY32JVN
如果您不喜欢，请看这里：
看看这个：http://explainextend .com/2010/04/18/hierarchical-query-in-mysql-limiting-parents/
of course its possible, but not very efficient.
you should go with nested set structures:
http://intelligent-enterprise.informationweek.com/001020/celko1_1.jhtml;jsessionid=AFUXE0ZF4PTNXQE1GHPSKH4ATMY32JVN
if you dont like that have a look here:
have a look at this: http://explainextended.com/2010/04/18/hierarchical-query-in-mysql-limiting-parents/
如果，正如您所说，只有两个级别的类别，那么简单的联接/别名查询就可以正常工作。如果您允许任意深度，那么您就必须使用奇特的递归查询或邻接集等等。
假设您只允许将项目附加到“较低”类别，那么类似的内容应该可以为您提供所需的结果：
如果您只需要查看顶级类别，则更改
WHERE子句视情况而定。if, as you say there's only two levels of categories, then a simple join/alias query will work just fine. If you were allowing arbitrary depths, then you'd have to go with the fancy recursive queries or adjancency sets and whatnot.
Assuming you only allow items to be attached to the 'lower' category, then something like should get you the results you need:
If you need to look at only top-level categories, then change the
WHEREclause as appropriate.如果您正在考虑 文档 所描述的替代方法，该文档由 Jeff Dege，到您当前的邻接列表树结构：
嵌套集对于经常读取的数据来说非常快但很少进行更改（读取使用 SQL
BETWEEN和索引；更改的成本可能会很高，因为可能需要更新许多现有记录），而路径枚举（也称为 物化路径）在使用索引和LIKE '[path]%'查询时提供可接受的良好读取性能（至少据我所知对于MySQL）以及插入操作和的良好性能将一个类别移动到另一个类别时可接受的性能。我个人有一个项目，其中使用路径枚举，其中数据库 ID 作为路径元素，并使用点
.来分隔元素（例如祖先路径1.2.3.）。您可能想要进行自己的基准测试来比较这些方法，特别是如果您有很多类别（数千个或更多）。
If you are considering the alternative approaches described by the document referenced by Jeff Dege, to your current Adjacency List tree structure:
Nested Sets are very fast for data that is read often but changes rarely (reads use SQL
BETWEENand indexes; changes can be expensive as they might have to update many existing records), while Path Enumeration (also known as Materialized Path) provides acceptable to good read performance when using indexes andLIKE '[path]%'queries (at least for MySQL as far as I know) and good performance for insertion operations and acceptable performance when moving a category to a different category.I personally have a project where I'm using Path Enumeration with database IDs as path elements and a dot
.to separate the elements (e.g. ancestor path1.2.3.).You might want to conduct your own benchmarks to compare these approaches, especially if you have lots of categories (several thousands or more).