php - 给定一组格式为 {FIXED PREFIX}/{NUMERIC ID}/... 的 URL，如何检索具有最高 {NUMERIC ID} 的 URL？

Question

我想查询包含艺术家姓名和相应的封面视频，这是链接列中的最高 ID。在此示例中，第二行带有 ..embd/125/...链接格式永远不会改变。我正在使用 PHP、MySQL 和 innodb 引擎

我首先对这两个表进行了 INNER JOIN:

Id | Artist
1    John Doe
2    Jane Doe

还有这个

Id | Id_Artist | Link 
53   1           http://example.com/embd/123/video/...
54   1           http://example.com/embd/125/video/...
55   1           http://example.com/embd/124/video/...   
56   1           http://example.com/embd/122/video/... 
57   2           http://example.com/embd/128/video/...
58   2           http://example.com/embd/129/video/...    
59   2           http://example.com/embd/130/video/...  

输出所有艺术家的预期结果，他们是视频

SELECT fp.id, fp.name, gl.link AS Video
FROM feed_post_artist AS fp
INNER JOIN gallery gl ON gl.id_artist = fp.id

Id | Id_Artist | Link 
 1   John Doe    http://example.com/embd/123/video/...
 1   John Doe    http://example.com/embd/125/video/...
 1   John Doe    http://example.com/embd/124/video/...   
 1   John Doe    http://example.com/embd/122/video/... 
 2   Jane Doe    http://example.com/embd/128/video/...
 2   Jane Doe    http://example.com/embd/129/video/...    
 2   Jane Doe    http://example.com/embd/130/video/... 

是否可以使用正则表达式比较每个艺术家的视频并只返回最高的链接 ID 作为结果？ (前4行125最高，后3行130最高)。我试过使用 MAX() 但它显然不起作用。

Id | Id_Artist | Link 
 1   John Doe    http://example.com/embd/125/video/...
 2   Jane Doe    http://example.com/embd/130/video/... 

编辑:

正如 Julian 在评论中建议的那样，MySQL SUBSTR() 解决了这种特殊情况。但是，当 id 超过 999 时，它将停止工作。

SELECT fp.id, fp.name, SUBSTR(gl.link,28,5) AS Video
FROM feed_post_artist AS fp
INNER JOIN gallery gl ON gl.id_artist = fp.id

唯一缺少的部分是将结果分组以仅返回 MAX()

Answer 1

select      a.Id
           ,a.Artist
           ,g.Link

from                    feed_post_artist    as a

            left join  (select      Id_Artist
                                   ,substr(max(concat(lpad(substring_index(substring_index(Link,'/',5),'/',-1),10,'0'),Link)),11)   as Link  

                        from        gallery as g

                        group by    Id_Artist
                        ) g

            on          g.Id_Artist =
                        a.Id

order by    a.Id                               

---

+----+----------+---------------------------------------+
| Id | Artist   | Link                                  |
+----+----------+---------------------------------------+
| 1  | John Doe | http://example.com/embd/125/video/... |
+----+----------+---------------------------------------+
| 2  | Jane Doe | http://example.com/embd/130/video/... |
+----+----------+---------------------------------------+