按照自定义顺序排序时忽略单词“the”。

Question

我正在尝试对标题列表进行排序，但当前有一大块以“The”开头的标题。 我希望忽略这个“The”，并根据第二个单词进行排序。SQL能实现吗？还是我需要在前端进行自定义操作？

例如，当前排序如下：

• Airplane
• Children of Men
• Full Metal Jacket
• Pulp Fiction
• The Fountain
• The Great Escape
• The Queen
• Zardoz

更好的排序应该是：

• Airplane
• Children of Men
• The Fountain
• Full Metal Jacket
• The Great Escape
• Pulp Fiction
• The Queen
• Zardoz

就像记录存储为“Fountain, The”之类的方式一样。 但如果可以的话，我不想以这种方式存储它们，这当然是问题的关键。

- dimo414

---

谢谢你提醒我关于扎多兹的事情。现在我会做关于浮动头的噩梦了。 - MusiGenesis

我很高兴有人注意到了那个问题 :D - dimo414

现在，《钢铁雄心》是一部很棒的电影。或者正如我对我的约会对象所说：“我...处于$#!+的世界中。” - MusiGenesis

Answer 1

最好创建一个计算列来完成这个任务，这样你可以索引该计算列并按照它进行排序。否则，排序将需要很多工作。

因此，你可以将计算列设置为：

CASE WHEN title LIKE 'The %' THEN stuff(title,1,4,'') + ', The' ELSE title END

编辑：如果MySQL中没有STUFF函数，则使用RIGHT或SUBSTRING函数删除前4个字符。但尽可能仍然要使用计算列，以便索引更好。相同的逻辑应适用于剥离“A”和“An”。

Rob

- Rob Farley

---

你可能还想将排序列全部转换为小写字母，以获得不区分大小写的排序（和搜索）。 - Thilo

是的，如果您有大小写敏感的情况。但我想像电影标题一样，大小写可能在输入中受控制，因此您不需要根据它们的小写等价物找到记录所带来的性能损失。 - Rob Farley

Answer 2

类似于：

ORDER BY IF(LEFT(title,2) = "A ",
            SUBSTRING(title FROM 3),
            IF(LEFT(title,3) = "An ",
              SUBSTRING(title FROM 4),
              IF(LEFT(title,4) = "The ",
                 SUBSTRING(title FROM 5),
                 title)))

但是，考虑到这样做的开销超过几次，最好将标题排序值存储在另一列中...

- great_llama

Answer 3

我认为你可以做类似以下的事情：

ORDER BY REPLACE(TITLE, 'The ', '')

尽管这将替换所有出现的“ The ”为“”，而不仅仅是第一个“ The ”，但我认为这不会对很多事情产生影响。

- MusiGenesis

---

我也不喜欢我的想法，但至少解释一下它是否不是最佳解决方案或者它是否无法运行。 - MusiGenesis

Answer 4

处理这个问题的最佳方法是拥有一个列，其中包含您想要用于排序输出的值。然后，您只需要使用：

  SELECT t.title
    FROM MOVIES t
ORDER BY t.order_title

关于标题的排序，将会有各种规则需要遵守。

根据您的示例，另一种选择是使用类似以下内容：

   SELECT t.title
    FROM MOVIES t
ORDER BY SUBSTR(t.title, INSTR(t.title, 'The '))

您可以使用CASE语句来包含各种规则。

- OMG Ponies

Answer 5

你可以动态地去掉"The"，但你很快会发现你还要处理"A"和"An"（除了像"A is for Alibi"这样的特殊情况）。当"外国"电影加入混合中时，你需要处理"El"和"La"（除了那个讨厌的边缘情况"LA Story"）。然后再混入一些德国电影，你就需要处理"Der"和"Die"（除了那些讨厌的"Die Hard"边缘案例）。看到模式了吗？你走上了一条越来越长、越来越充满特殊情况的道路。

为了避免越来越多的特殊情况，前进的方法是将标题存储为您想要显示的方式 以及 将标题存储为您想要排序的方式。

- Dave W. Smith

---

非常好的观点，不过幸运的是在我的使用场景中并不需要完美，所以那些边缘情况可以被忽略，至少暂时是这样。但还是要加一分，提及了这个问题。 - dimo414

Answer 6

只会删除第一个 The 的方法：

=SUBSTITUTE(A1,"The ","",1) OR more reliably: 

=IF(IF(LEFT(A1,4)="The ",TRUE)=TRUE,RIGHT(A1,(LEN(A1)-4)),A1)

第二个是基本上说如果第一个左侧数字等于The，那么检查单元格中有多少位数字，并仅显示右侧数字，不包括The。

- Aiken

Answer 7

对于SQLite

ORDER BY CASE WHEN LOWER(SUBSTR(title,1,4)) = 'the ' THEN SUBSTR(title,5) ELSE title END ASC

- sergey.n