自定义ORDER BY忽略"the" [英] Custom ORDER BY to ignore 'the'

问题描述

我正在尝试对标题列表进行排序，但是目前有大量的标题以'The'开头.我希望忽略'The'，并按第二个单词排序.在SQL中有可能吗?还是我必须在前端进行自定义工作?

I'm trying to sort a list of titles, but currently there's a giant block of titles which start with 'The '. I'd like the 'The ' to be ignored, and the sort to work off the second word. Is that possible in SQL, or do I have to do custom work on the front end?

例如，当前排序:

• 飞机
• 男人的孩子
• 全金属外套
• 低俗小说
• 喷泉
• 大逃亡
• 女王
• Zardoz

最好进行排序:

• 飞机
• 男人的孩子
• 喷泉
• 全金属外套
• 大逃亡
• 低俗小说
• 女王
• Zardoz

几乎就像将记录存储为喷泉，The"之类.但是，如果可以的话，我不想那样存储它们，这当然是问题的症结所在.

Almost as if the records were stored as 'Fountain, The', and the like. But I don't want to store them that way if I can, which is of course the crux of the problem.

推荐答案

最好是使用一个计算列来执行此操作，以便您可以以此为索引计算列和顺序.否则，排序工作会很繁琐.

Best is to have a computed column to do this, so that you can index the computed column and order by that. Otherwise, the sort will be a lot of work.

因此，您可以将计算列设为:

So then you can have your computed column as:

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

如果MySQL中不提供STUFF，则使用RIGHT或SUBSTRING删除前4个字符.但是，如果可能的话，仍然尝试使用计算列，以便更好地建立索引.删除"A"和"An"应采用相同的逻辑.

If STUFF isn't available in MySQL, then use RIGHT or SUBSTRING to remove the leading 4 characters. But still try to use a computed column if possible, so that indexing can be better. The same logic should be applicable to rip out "A " and "An ".

Rob

这篇关于自定义ORDER BY忽略"the"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！