Mysql层次结构路径

Question

我正在使用 this hierarchy格式来构建类别的层次结构。我正在尝试构建搜索以使用全文索引搜索提示表。进行搜索工作正常，但我想从类别表中获取层次结构列，其中每个返回的行由 / 分隔。

示例:
假设返回看起来像这样:

+---------------+-------------+
| category_name | title       |
+---------------+-------------+
| Computers     | How to jump |
| Video Games   | How to jump |
| Super Mario   | How to jump |
+---------------+-------------+

相反，我怎样才能让返回看起来像这样:

+-----------------------------------+-------------+
| category_path                     | title       |
+-----------------------------------+-------------+
| Computers/Video Games/Super Mario | How to jump |
+-----------------------------------+-------------+

类别表

mysql> describe categories;
+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| category_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| category_name | char(60) | NO   |     | NULL    |                |
| lft           | int(11)  | NO   |     | NULL    |                |
| rgt           | int(11)  | NO   |     | NULL    |                |
+---------------+----------+------+-----+---------+----------------+
4 rows in set (0.07 sec)

提示表

mysql> describe tips;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| tip_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| category_id | int(11)      | NO   | MUL | NULL    |                |
| title       | varchar(100) | NO   | MUL | NULL    |                |
| tip         | text         | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.07 sec)

这是我到目前为止的查询内容

select * from tips,
categories AS node,
categories AS parent
where match (tips.title, tips.tip) against (? in boolean mode)
AND node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_name = ?
AND parent.lft != 1
ORDER BY node.lft

这是我的最终结果:

select title, group_concat(parent.category_name order by parent.lft separator '/') as category_path
from tips, categories as node, categories as parent
where match (tips.title, tips.tip) against ('button' in boolean mode)
and tips.category_id = node.category_id
and node.lft between parent.lft and parent.rgt
and parent.lft != 1
group by title;

Answer 1

让我来回答你的问题。如果您有这样的数据:

+---------------+-------------+
| category_name | title       |
+---------------+-------------+
| Computers     | How to jump |
| Video Games   | How to jump |
| Super Mario   | How to jump |
+---------------+-------------+

这样得到它:

+-----------------------------------+-------------+
| category_path                     | title       |
+-----------------------------------+-------------+
| Computers/Video Games/Super Mario | How to jump |
+-----------------------------------+-------------+

你会这样做:

select group_concat(category_name separator '/'), title
from t
group by title;

然后你交叉手指。此查询未指定顺序。如果我假设原始结果有一个 id 或 creationdate 或 depth 或 something 指定顺序，那么我可以这样做:

select group_concat(category_name separator '/' order by id), title
from t
group by title;