如何在SQL中排序，而忽略文章(“，"，"a"，"an"等) [英] How to sort in SQL, ignoring articles ('the", "a', "an" etc)

问题描述

这个问题很多，我可以看到它在 Ruby 和 Drupal ，但我没有看到它用于SQL.

This comes up a lot, and I can see it's come up on StackOverflow for XSLT, Ruby and Drupal but I don't see it specifically for SQL.

问题是，当标题以"The"，"A"或"An"开头时，如何正确排序标题?

So the question is, how do you sort titles correctly when they begin with "The", "A", or "An"?

一种方法就是简单地对这些字符串进行TRIM():

One way is simply to TRIM() those strings:

ORDER BY TRIM( 
  LEADING 'a ' FROM 
  TRIM( 
    LEADING 'an ' FROM 
    TRIM( 
      LEADING 'the ' FROM LOWER( title ) 
      ) 
    ) 
  )

这是建议在AskMeFi上使用(需要LOWER()功能吗?).

which was suggested on AskMeFi a while back (does it need that LOWER() function?).

我知道我也已经看到了这种情况的Case/Switch实现，但是Google很难做到这一点.

I know I've also seen some kind of Case/Switch implementation of this but it's a little hard to Google for.

显然，有许多可能的解决方案.最好对影响性能的SQL专家进行权衡.

Obviously there are a number of possible solutions. What would be good is SQL gurus weighing in on which have performance implications.

推荐答案

我见过的一种方法是具有两列-一个用于显示，另一个用于排序:

One approach I've seen was to have two columns - one for display and the other for sorting:

description  |  sort_desc
----------------------------
The the      | the, The
A test         | test, A
I, Robot      | i, Robot

我还没有进行任何实际的测试，但这具有能够使用索引的优点，并且每次您想按描述进行排序时都不需要字符串操作.除非您的数据库支持实例化视图(MySQL不支持)，否则将逻辑作为视图中的计算列实现不会带来任何好处，因为您无法为计算列编制索引.

I haven't done any real world testing, but this has the benefit of being able to use an index and doesn't require string manipulation every time you want to order by the description. Unless your database supports materialized views (which MySQL doesn't), implementing the logic as a computed column in a view wouldn't provide any benefit because you can't index the computed column.

这篇关于如何在SQL中排序，而忽略文章(“，"，"a"，"an"等)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！