按字符串的一部分连接

Question

我有以下表格：

**visitors**
+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| visitors_id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| visitors_path       | varchar(255) | NO   |     |         |                |
+---------------------+--------------+------+-----+---------+----------------+

**fedora_info**
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| pid            | varchar(255) | NO   | PRI |         |       |
| owner_uid      | int(11)      | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

首先，我通过以下方法寻找与特定页面相关的visitors_path：

SELECT visitors_id, visitors_path
FROM visitors
WHERE visitors_path REGEXP '[[:<:]]fedora/repository/.*:[0-9]+$';

以上查询返回预期结果。

现在，上面的查询中的.*:[0-9]+引用了第二个表中的pid。现在，我想知道第二个表中按owner_uid分组的上述查询的结果计数。

我怎样才能加入这张桌子？

编辑

样本数据：

visitors
+-------------+---------------------------------+
| visitors_id | visitors_path                   |
+-------------+---------------------------------+
|        4574 | fedora/repository/islandora:123 |
|        4575 | fedora/repository/islandora:123 |
|        4580 | fedora/repository/islandora:321 |
|        4681 | fedora/repository/islandora:321 |
|        4682 | fedora/repository/islandora:321 |
|        4704 | fedora/repository/islandora:321 |
|        4706 | fedora/repository/islandora:456 |
|        4741 | fedora/repository/islandora:456 |
|        4743 | fedora/repository/islandora:789 |
|        4769 | fedora/repository/islandora:789 |
+-------------+---------------------------------+

fedora_info
+-----------------+-----------+
| pid             | owner_uid |
+-----------------+-----------+
| islandora:123   |         1 |
| islandora:321   |         2 |
| islandora:456   |         3 |
| islandora:789   |         4 |
+-----------------+-----------+

Expected result:
+-----------------+-----------+
| count           | owner_uid |
+-----------------+-----------+
| 2               |         1 |
| 4               |         2 |
| 3               |         3 |
| 2               |         4 |
| 0               |         5 |
+-----------------+-----------+

Answer 1

我建议您使用规范您的数据库。在visitors中插入行时，提取前端语言中的pid并将其放在单独的列(例如fi_pid)中。那么你就可以很容易地加入它。

下面的查询可能适用于您。但它的cpu强度很小。

SELECT 
       COUNT(a.visitors_id) as `count`,
       f.owner_uid
FROM   (SELECT visitors_id, 
               visitors_path, 
               SUBSTRING(visitors_path, ( LENGTH(visitors_path) - 
                                          LOCATE('/', REVERSE(visitors_path)) ) 
                                        + 2) AS 
                      pid 
        FROM   visitors 
        WHERE  visitors_path REGEXP '[[:<:]]fedora/repository/.*:[0-9]+$') AS `a`

JOIN fedora_info AS f 
         ON ( a.pid = f.pid ) 

GROUP  BY f.owner_uid 

Answer 2

下面的查询返回预期的结果，但是它的Query took 9.6700 sec非常慢

SELECT COUNT(t2.pid), t1.owner_uid
FROM fedora_info t1
JOIN (SELECT TRIM(LEADING 'fedora/repository/' FROM visitors_path) as pid
FROM visitors
WHERE visitors_path REGEXP '[[:<:]]fedora/repository/.*:[0-9]+$') t2 ON t1.pid = t2.pid
GROUP BY t1.owner_uid