我正在尝试对标题列表进行排序,但是目前有大量的标题以'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"
发布评论