时间戳

首页 > 解决方案 > 按大小写字母排序

firebird - 按大小写字母排序

问题描述

问题不是太具体，但不确定如何很好地解释这个问题。我的数据库中有表，字段中有名称。如果名称以某些字母开头，我想以某种方式对名称进行排序，首先排序，依此类推。我现在拥有的是

SELECT
  (T.firstname||' '||T.lastname) as Full_Name
FROM 
  TABLE T
ORDER BY
  CASE
    WHEN LPAD(T.firstname, 1) = 'J' THEN T.firstname
    WHEN LPAD(T.firstname, 1) = 'B' THEN T.firstname
  END DESC,
Full_Name ASC

现在这返回了我想看到的，以“J”开头的名称首先排序，然后是“B”，然后是其余的。但是，结果看起来像

What I get    What I want

Full_Name     Full_Name
----------    ----------
Junior MR     James A
John Doe      Joe Bob
Joe Bob       John Doe
James A       Junior MR
Brad T        B Test
Bob Joe       Bb Test
Bb Test       Bob Joe
B Test        Brad T
A Test        A Test
Aa Test       Aa Test
AFLKJASDFJ    AFLKJASDFJ
Ann Doe       Ann Doe

但我想要的是 J 和 B 也按字母顺序排序，现在它正在按字母顺序倒序排列。如何在案例中指定订单？我尝试为以'J'和'B'开头的不同情况使用2个单独的case语句，它只是向我展示了相同的结果

标签： firebird

解决方案

只需在 select 运行时执行一个额外的列，material usingtriggers或 volatile using 表达式，然后在排序中使用它。

对于二次排序，使用名称的原始组成部分，而不是将两个名称放在一起的表达式，从而破坏了哪个是哪个的信息。

示例：https ://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=fbf89b3903d3271ae6c55589fd9cfe23

 create table T (
   firstname varchar(10),
   lastname varchar(10),
   fullname computed by ( 
       Coalesce(firstname, '-') || ' ' || Coalesce(T.lastname, '-')  
   ),
   sorting_helper computed by (
     CASE WHEN firstname starting with 'J' then 100
          WHEN firstname starting with 'B' then 50
          ELSE 0
     END
   )
 )

注意重要的区别：我的助手表达式是“排名”之一。它产生几个预定义的排名之一，因此将“James”和“Joe”放入具有完全相同排名值的同一箱中。您的表达式仍然会产生名称本身，从而错误地保持这些名称之间的差异。但是您不希望这种差异，您告诉您希望所有以 J 开头的名称都向上移动，然后按照通常的规则在它们之间进行排序。所以，就照你说的做，做出一个将所有 J 名称组合在一起的表达式，而不区分它们。

 insert into T
   select 
     'John', 'Doe'  
   from rdb$database union all select
     'James', 'A'  
   from rdb$database union all select
     'Aa ', 'Test'  
   from rdb$database union all select
     'Ann', 'Doe'  
   from rdb$database union all select
     'Bob', 'Joe'  
   from rdb$database union all select
     'Brad', 'Test'  
   from rdb$database union all select
     NULL, 'Smith'  
   from rdb$database union all select
     'Ken', NULL  
   from rdb$database 

受影响的 8 行

 select * from T

名字 | 姓氏 | 全名 | SORTING_HELPER
:-------- | :------- | :------------ | -------------:
约翰 | 能源部 | 约翰·多伊 | 100
詹姆斯 | 一个 | 詹姆斯 A | 100
啊 | 测试 | Aa 测试 | 0
安 | 能源部 | 安多伊 | 0
鲍勃 | 乔 | 鲍勃·乔 | 50
布拉德 | 测试 | 布拉德测试 | 50
空      | 史密斯 | - 史密斯 | 0
肯 | 空     | 肯- | 0

 Select FullName from T order by sorting_helper desc, firstname asc, lastname asc

| 全名 |
| :------------ |
| 詹姆斯 A |
| 约翰·多伊 |
| 鲍勃·乔 |
| 布拉德测试 |
| - 史密斯 |
| Aa 测试 |
| 安多伊 |
| 肯- |

或无computed-by柱

 Select FullName from T order by (CASE WHEN firstname starting with 'J' then 0
          WHEN firstname starting with 'B' then 1
          ELSE 2
     END) asc, firstname asc, lastname asc

| 全名 |
| :------------ |
| 詹姆斯 A |
| 约翰·多伊 |
| 鲍勃·乔 |
| 布拉德测试 |
| - 史密斯 |
| Aa 测试 |
| 安多伊 |
| 肯- |

对于缺少名称或姓氏的行的定位的额外调整，您还可以使用NULLS FIRST或NULLS LAST选项，如https://firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/nullguide-sorts.html上的 Firebird 文档中所述

然而，这种方法的问题是，在足够大的表上，您将无法使用基于姓名和姓氏构建的索引进行排序，而是不得不求助于未排序的数据提取（也就是NATURAL SORT在阅读时QUERY PLAN）然后将其分类到磁盘上的临时文件中。在足够大的数据上，这可能会变得非常缓慢且消耗大量数据。

您可以尝试通过使用您的排名表达式创建“表达式索引”来使其变得更好。并希望 FB 优化器会使用它（使用像这样的冗长表达式非常棘手CASE）。坦率地说，你可能仍然没有它（至少我没有设法让 FB 2.1 在那里使用 index-by-case-expression）。

您可以将排名表达式“具体化”为常规SmallInt Not Null列而不是一列，COMPUTED BY并使用类型保持该列填充适当的数据。然后，您可以在该常规列上创建常规索引。虽然它会为每一行添加两个字节，但这并没有太大的增长。TRIGGERBEFORE UPDATE OR INSERT

但即便如此，具有很少不同值的索引也不会增加多少价值，它会具有“低选择性”。此外，index-by-expression 不能为compound一（意思是，包括表达式之后的其他列）。

因此，对于大数据，您实际上最好使用三个不同的查询融合在一起。添加脚手架，如果您还没有这样做：

create index i58647579_names on T58647579 ( firstname, lastname )

然后你可以像这样进行三重选择：

WITH S1 as (
  select FullName from T58647579
  where firstname starting with 'J' 
  order by firstname asc, lastname asc
), S2 as (
  select FullName from T58647579
  where firstname starting with 'B'
  order by firstname asc, lastname asc
), S3 as (
  select FullName from T58647579
  where (firstname is null)
     or (  (firstname not starting with 'J')
       and (firstname not starting with 'B')
        )
  order by firstname asc, lastname asc
)
SELECT * FROM S1
   UNION ALL
SELECT * FROM S2
   UNION ALL
SELECT * FROM S3

虽然您将遍历该表三次 - 您将通过预排序索引来执行此操作：

PLAN (S1 T58647579 ORDER I58647579_NAMES INDEX (I58647579_NAMES))
PLAN (S2 T58647579 ORDER I58647579_NAMES INDEX (I58647579_NAMES))
PLAN (S3 T58647579 ORDER I58647579_NAMES)

推荐阅读