如何在 SQL 中排序，忽略冠词（“the”、“a”、“an”等）

Question

这个问题出现了很多次，我可以看到它出现在 StackOverflow 上的 XSLT、Ruby 和Drupal 但我没有看到它专门用于 SQL。

所以问题是，当标题以“The”、“A”或“An”开头时，如何正确排序？

一种方法是简单地 TRIM() 这些字符串：

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

这是 不久前在 AskMeFi 上建议（它需要 LOWER() 函数吗？）。

我知道我也见过某种 Case/Switch 实现，但在 Google 上搜索有点困难。

显然，有多种可能的解决方案。如果 SQL 专家能权衡一下对性能的影响，那就太好了。

Answer 1

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

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

我没有进行任何实际测试，但这具有能够使用索引并且不需要字符串操作的好处每次您想按说明订购时。除非您的数据库支持物化视图（MySQL 不支持），否则将逻辑实现为视图中的计算列不会带来任何好处，因为您无法对计算列建立索引。

Answer 2

我已经使用这个多年了，但不记得在哪里找到它

SELECT 
CASE
    WHEN SUBSTRING_INDEX(Title, ' ', 1) IN ('a', 'an', 'the') 
    THEN CONCAT( SUBSTRING( Title, INSTR(Title, ' ') + 1 ), ', ', SUBSTRING_INDEX(Title, ' ', 1) ) 
    ELSE Title 
    END AS TitleSort,
Title AS OriginalTitle 
FROM yourtable 
ORDER BY TitleSort 

：

TitleSort                  | OriginalTitle
------------------------------------------------------
All About Everything       | All About Everything
Beginning Of The End, The  | The Beginning Of The End
Interesting Story, An      | An Interesting Story
Very Long Story, A         | A Very Long Story

Answer 3

特别是对于 Postgres，您可以使用 regexp_replace 来为您完成这项工作：

BEGIN;
CREATE TEMPORARY TABLE book (name VARCHAR NOT NULL) ON COMMIT DROP;
INSERT INTO book (name) VALUES ('The Hitchhiker’s Guide to the Galaxy');
INSERT INTO book (name) VALUES ('The Restaurant at the End of the Universe');
INSERT INTO book (name) VALUES ('Life, the Universe and Everything');
INSERT INTO book (name) VALUES ('So Long, and Thanks for All the Fish');
INSERT INTO book (name) VALUES ('Mostly Harmless');
INSERT INTO book (name) VALUES ('A book by Douglas Adams');
INSERT INTO book (name) VALUES ('Another book by Douglas Adams');
INSERT INTO book (name) VALUES ('An omnibus of books by Douglas Adams');

SELECT name FROM book ORDER BY name;
SELECT name, regexp_replace(lower(name), '^(an?|the) (.*)
, '\2, \1') FROM book ORDER BY 2;
SELECT name FROM book ORDER BY regexp_replace(lower(name), '^(an?|the) (.*)
, '\2, \1');
COMMIT;

Answer 4

我只能代表 SQL Server：您在 CASE 语句中使用 LTRIM。不需要 LOWER 函数，因为默认情况下选择不区分大小写。但是，如果您想忽略文章，那么我建议您使用干扰词词典并设置全文索引目录。我不确定其他实现是否 SQL 支持这一点。

Answer 5

需要LOWER。虽然 SELECT 不区分大小写，但 ORDER BY 却区分大小写。

Answer 6

尝试以下操作：

ORDER BY
替换（替换（替换（YOURCOLUMN，'THE'，''），'a\''，''），'an'，''）

未测试！