如何正确排序包含续集编号的电影名称（varchar）？

发布于 2024-10-20 20:01:25 字数 969 浏览 6 评论 0原文

假设我有一张电影表。电影的名称是 varchar。但有时电影名称包含超过 9 的续集（或系列或剧集）编号。如何对电影名称进行排序以像人类一样处理数字？

默认情况下它是这样排序的：

select * from movies order by name

Alpha
Beta
Gamma 1
Gamma 10
Gamma 2
Gamma 3
Gamma 4
Gamma 5
Gamma 6
Gamma 7
Gamma 8
Gamma 9
Delta

看到 1 后面的 10 了吗？我知道它为什么这样做，我想知道的是如何让它像这样排序：

Alpha
Beta
Gamma 1
Gamma 2
Gamma 3
Gamma 4
Gamma 5
Gamma 6
Gamma 7
Gamma 8
Gamma 9
Gamma 10
Delta

我到处寻找解决方案，但没有找到任何解决这个问题的方法。

请注意，这只是一个简单的示例，实际名称可能要复杂得多，例如 Gamma The Explorer 1.episode 或 Gamma The Explorer 2.Series 1.episode或 Gamma The Explorer 3. Episode Director's Cut 并且大多数其他电影名称中没有数字，因此请不要使用依赖于字符串中特定位置或位置的数字的解决方案，或者使用 name+0 排序 - 这不是我想要的解决方案。

我知道 Windows 资源管理器以及其他软件以正确的（人类）方式处理文件名排序，并且我不相信 MySQL 无法处理如此明显的事情，所以应该有一个可行的方法对此的解决方案。

编辑：显然它被称为自然排序。 MySQL真的没有这个吗？可选：什么是 UDF，在哪里可以获取用于自然排序的 UDF，以及如何在我的服务器上获取它？

原文

Let's say I have a table of movies. The name of the movie is a varchar. But sometimes the movie names contain sequel (or series or episode) numbers that go over 9. How can I sort the movie name to handle numbers like a human would?

This is how it sorts by default:

select * from movies order by name

Alpha
Beta
Gamma 1
Gamma 10
Gamma 2
Gamma 3
Gamma 4
Gamma 5
Gamma 6
Gamma 7
Gamma 8
Gamma 9
Delta

See the 10 after 1? I know why it does that, what I want to know is how to make it sort like this:

Alpha
Beta
Gamma 1
Gamma 2
Gamma 3
Gamma 4
Gamma 5
Gamma 6
Gamma 7
Gamma 8
Gamma 9
Gamma 10
Delta

I have looked for solutions all over and haven't found anything that solves this.

Please note that this is just a simple example, the actual names can be much more complex, like Gamma The Explorer 1. episode or Gamma The Explorer 2. series 1. episode or Gamma The Explorer 3. episode Director's Cut and that most other movie names don't have numbers in them, so please no solutions that count on the number being in a specific place or position in the string or sorting using name+0 - that's not the solution I want.

I know for a fact that Windows Explorer, among other software, handles sorting file names in the correct (human) fashion, and I don't believe MySQL wouldn't be able to handle something so obvious, so there ought to be a feasible solution for this.

Edit: Apparently it's called natural sorting. Does MySQL really not have this at all? Optionally: What is UDF, and where do I get one for natural sorting, and how do I get it on my server?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情深已缘浅 2024-10-27 20:01:25

  SELECT movies.*, 
         SUBSTRING_INDEX(name, ' ', 1) AS name_str,
         SUBSTRING_INDEX(name, ' ', -1) + 0 AS name_num
    FROM movies
ORDER BY name_str, name_num

  SELECT movies.*, 
         SUBSTRING_INDEX(name, ' ', 1) AS name_str,
         SUBSTRING_INDEX(name, ' ', -1) + 0 AS name_num
    FROM movies
ORDER BY name_str, name_num

回复收藏 0 原文

~没有更多了~