如何优化mysql查询获取类别和子类别 [英] How to optimize mysql query getting categories and subcategories

问题描述

我创建了一个脚本，该脚本构建带有类别，子类别和子子类别的菜单. 为了获得这些项目，我使用了3个独立的MySQL查询.如下:

I created a script which builds a menu with categories, sub-categories and sub-sub-categories. To get these items I use 3 separate MySQL queries. As follows:

要检索主要类别:

SELECT id, name FROM categories WHERE parent_id=0

要检索我使用的每个类别的子类别:

To retrieve the sub-categories for each category I use:

SELECT id, name FROM categories WHERE parent_id=?

哪里?是父类别的ID. 然后，我再次为每个子类别运行查询.

Where the ? is the id of the parent category. I then run the query again for each sub-category.

如何优化此脚本以使用较少的sql查询? 预先谢谢你.

How can I optimise this script to use less sql queries ? Thank you in advance.

推荐答案

尝试此查询-

类别+子类别:

SELECT c1.id, c1.name, c2.id, c2.name FROM categories c1
  LEFT JOIN categories c2
    ON c2.parent_id = c1.id
WHERE c1.parent_id = 0

---

类别+子类别+子子类别:

---

categories+sub categories+sub sub categories:

SELECT c1.id, c1.name, c2.id, c2.name, c3.id, c3.name FROM categories c1
  LEFT JOIN categories c2
    ON c2.parent_id = c1.id
  LEFT JOIN categories c3
    ON c3.parent_id = c2.id
WHERE c1.parent_id = 0

这篇关于如何优化mysql查询获取类别和子类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！