然后您可以这样选择view_count:
我有一个表,其中包含访问过哪个节点的数据。一个节点可能会被访问多次。为此,我有另一个表,其中包含访问节点、之前访问的节点和之后访问的节点的数据。我现在想使用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 |
+---------+--------+
任何帮助都将不胜感激。
更改您的设计,使其有1个表访问:
+----+------+------+|id|user|节点|+----+------+------+|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||2015年10月10日|+----+------+------+
然后您可以这样选择view_count:
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