PHP 与MySQL - 只需一个查询即可获取顶级类别

发布于 2024-12-07 04:04:40 字数 292 浏览 0 评论 0原文

我正在尝试通过 MySQL 进行一次查询，从子类别（可能是任何级别）获取第一个类别（级别为零，无父级）。这可能吗？

下面是一个示例 DB 结构：

Table categories

    id (INT 11)
    parent (INT 11, zero if it has no parent)
    name

编辑：MPTT 会是解决该问题的更简单的方法吗？

原文

I'm trying to get the first category (level zero, no parents) from a child category (which might be any level) with just one query with MySQL. Is that possible?

Here's an example DB structure :

Table categories

    id (INT 11)
    parent (INT 11, zero if it has no parent)
    name

edit: Would MPTT be a easier solution to the problem?

分享到QQ

分享到微博

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

发布评论

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

醉生梦死 2024-12-14 04:04:40

是的，使用存储函数和递归。你可能会陷入无限循环，所以要小心。

回复收藏 0 原文

季末如歌 2024-12-14 04:04:40

仅使用一个查询来扫描无限级别是不可能的，但如果您确定类别树的深度不超过一定数量的级别，您可以使用这一技巧，这适用于 4 个级别：

SELECT COALESCE(c4.parent, c3.parent, c2.parent, c1.parent)
 FROM categories AS c1
 LEFT JOIN categories AS c2 ON( c1.parent = c2.id )
 LEFT JOIN categories AS c3 ON( c2.parent = c3.id )
 LEFT JOIN categories AS c4 ON( c3.parent = c4.id )
 WHERE c1.id = {someID}

It is impossible to scan infinite levels with only one query but if you are sure your categoies tree doesn't go deeper than a certain number of levels you can use this trick, this is for 4 levels:

SELECT COALESCE(c4.parent, c3.parent, c2.parent, c1.parent)
 FROM categories AS c1
 LEFT JOIN categories AS c2 ON( c1.parent = c2.id )
 LEFT JOIN categories AS c3 ON( c2.parent = c3.id )
 LEFT JOIN categories AS c4 ON( c3.parent = c4.id )
 WHERE c1.id = {someID}

回复收藏 0 原文

~没有更多了~