mysql - SQL节点路径重构

Question

我有一个表，其中包含有关已访问哪个节点的数据。一个节点可能会被多次访问。为此，我有另一个表，其中包含访问过的节点、之前访问过的节点和之后访问过的节点的数据。我现在想使用 MySQL 按访问顺序重建路径。我似乎无法弄清楚如何对此进行查询，所以我在这里寻求帮助。

示例

假设有人按以下顺序访问了这些节点:

4->5->6->7->4->6->10->12->7->15

表格看起来像这样:

访问次数

+---------+-------------------------------+----------+------------+
| id      | user                          | node     | view_count |
+---------+-------------------------------+----------+------------+
| 1       | l3lie1frl77j135b3fehbjrli5    | 4        | 2          |
+---------+-------------------------------+----------+------------+
| 2       | l3lie1frl77j135b3fehbjrli5    | 5        | 1          |
+---------+-------------------------------+----------+------------+
| 3       | l3lie1frl77j135b3fehbjrli5    | 6        | 2          |
+---------+-------------------------------+----------+------------+
| 4       | l3lie1frl77j135b3fehbjrli5    | 7        | 2          |
+---------+-------------------------------+----------+------------+
| 5       | l3lie1frl77j135b3fehbjrli5    | 10       | 1          |
+---------+-------------------------------+----------+------------+
| 6       | l3lie1frl77j135b3fehbjrli5    | 12       | 1          |
+---------+-------------------------------+----------+------------+
| 7       | l3lie1frl77j135b3fehbjrli5    | 15       | 1          |
+---------+-------------------------------+----------+------------+

重访

+---------+-------------------------------+-------+----------------+-----------------+
| id      | user                          | node  | after_visiting | before_visiting |
+---------+-------------------------------+-------+----------------+-----------------+
| 1       | l3lie1frl77j135b3fehbjrli5    | 4     |       7        |        6        |
+---------+-------------------------------+-------+----------------+-----------------+
| 2       | l3lie1frl77j135b3fehbjrli5    | 6     |       4        |       10        |
+---------+-------------------------------+-------+----------------+-----------------+
| 3       | l3lie1frl77j135b3fehbjrli5    | 7     |      12        |       15        |
+---------+-------------------------------+-------+----------------+-----------------+

我想构造一个查询，以字符串或节点列表的形式返回路径，如下所示:

4,5,6,7,4,6,10,12,7,15

或

+---------+--------+
| index   | node   |
+---------+--------+
|    1    |   4    |
+---------+--------+
|    2    |   5    |
+---------+--------+
|    3    |   6    |
+---------+--------+
|    4    |   7    |
+---------+--------+
|    5    |   4    |
+---------+--------+
|    6    |   6    |
+---------+--------+
|    7    |   10   |
+---------+--------+
|    8    |   12   |
+---------+--------+
|    9    |   7    |
+---------+--------+
|    10   |   15   |
+---------+--------+

任何帮助将不胜感激。

Answer 1

将您的设计更改为有 1 个表访问:

+----+------+------+| id | user | node |+----+------+------+|  1 | xx   |    4 ||  2 | xx   |    5 ||  3 | xx   |    6 ||  4 | xx   |    7 ||  5 | xx   |    4 ||  6 | xx   |    6 ||  7 | xx   |   10 ||  8 | xx   |   12 ||  9 | xx   |    7 || 10 | xx   |   15 |+----+------+------+

---

you can then select view_count like this:

select node, count(*) view_count
from visits
where user = :user
group by node

路径是这样的:

select group_concat(node order by id separator ',') path
from visits
where name = :name