主页 / dba / 问题 / 294070
Accepted
Taras
Asked: 2021-06-11 04:13:24 +0800 CST

在 SQL 查询中按名称和多音节数字对街道进行排序

  • 772

在 Oracle DB 中有一个包含一些数据的表：

落后 HNR 街道
00020 44 亚琛大街
00020 44/2 亚琛大街
00020 44/1 亚琛大街
00020 46 亚琛大街
00020 46/6 亚琛大街
00020 46/5 亚琛大街
00020 46/3 亚琛大街
00020 46/1 亚琛大街
00020 46/2 亚琛大街
00020 47 亚琛大街
00020 48 亚琛大街
00020 48/1 亚琛大街
00020 48/2 亚琛大街
00020 48A 亚琛大街

请在此处找到一个最小的可重现示例：http ://www.sqlfiddle.com/#!4/19d625/1

现在我正在尝试通过"HNR"按顺序对这些数据进行排序25 > 25A > 25B > 25/3 。因此，最高优先级有一个简单的数字，然后是带有字母的相同数字（也已排序），最后是带有子编号的相同数字/（已排序）。

使用此代码

SELECT F.LAG,
       F.HNR,
       F.STREET
FROM DB.F F
ORDER BY F.LAG,
         F.STREET,
         TO_NUMBER(regexp_substr(F.HNR, '[[:digit:]]{1,4}')),
         TO_CHAR(regexp_substr(F.HNR, '[[:alpha:]]{1}')),
         TO_NUMBER(regexp_substr(F.HNR, '[^/]+', 1, 2));

我得到这个：

落后 HNR 街道
00020 44/1 亚琛大街
00020 44/2 亚琛大街
00020 44 亚琛大街
00020 46/1 亚琛大街
00020 46/2 亚琛大街
00020 46/3 亚琛大街
00020 46/5 亚琛大街
00020 46/6 亚琛大街
00020 46 亚琛大街
00020 47 亚琛大街
00020 48/1 亚琛大街
00020 48/2 亚琛大街
00020 48A 亚琛大街
00020 48 亚琛大街

而不是这个：

落后 HNR 街道
00020 44 亚琛大街
00020 44/1 亚琛大街
00020 44/2 亚琛大街
00020 46 亚琛大街
00020 46/1 亚琛大街
00020 46/2 亚琛大街
00020 46/3 亚琛大街
00020 46/5 亚琛大街
00020 46/6 亚琛大街
00020 47 亚琛大街
00020 48 亚琛大街
00020 48A 亚琛大街
00020 48/1 亚琛大街
00020 48/2 亚琛大街

我究竟做错了什么？

我见过SQL: ORDER BY 在特定列中使用子字符串……可能吗？并按字母排序，然后按数字排序，但我没有发现任何相关的东西。

oracle query

3 个回答

  1. Best Answer

    除了对与您已经实现的那些字符相关的位进行排序之外，我还将根据中特殊字符的存在单独介绍排序。HNR

    您可以尝试使用REGEXP_COUNT它。您可以这样做：

    SELECT F.LAG,
       F.HNR,
       F.STREET
FROM DB.F F
ORDER BY F.LAG,
         F.STREET,
         TO_NUMBER(regexp_substr(F.HNR, '[[:digit:]]{1,4}')),
         regexp_count(F.HNR, '[/]'),  -- numbers with strokes follow those without strokes
         regexp_count(F.HNR, '[[:alpha:]]'),  -- no-letter numbers first
         TO_CHAR(regexp_substr(F.HNR, '[[:alpha:]]{1}')),
         TO_NUMBER(regexp_substr(F.HNR, '[^/]+', 1, 2));

    我在上面添加的两个附加排序标准将导致以下结果：

    HNR regexp_count(HNR, '[/]') regexp_count(HNR, '[[:alpha:]]')
    48 0 0
    48A 0 1
    48/1 1 0

    如您所见，按特定顺序使用它们将为 HNR 生成所需的排序顺序。

    在 db<>fiddle的 SQL Fiddle上查看此解决方案的现场演示。

    • 3

  2. PostgreSQL

    使用 PostgreSQL，您可以做到这一点，

    SELECT x
FROM ( VALUES ('25'), ('25A'), ('25B'), ('25/3') ) AS t(x)
CROSS JOIN LATERAL regexp_matches(x, '(\d+)(?:(\w+)|(?:\/(\d+)))?')
  AS re(m)
ORDER BY m[1], m[3] NULLS FIRST, m[2] NULLS FIRST;

    这里的关键是

    CROSS JOIN LATERAL regexp_matches(x, '(\d+)(?:(\w+)|(?:\/(\d+)))?')
  AS re(m)

    这将返回一个包含匹配项的 ARRAY，

      x   |       m        
------+----------------
 25   | {25,NULL,NULL}
 25A  | {25,A,NULL}
 25B  | {25,B,NULL}
 25/3 | {25,NULL,3}

    我们只想订购

    • 首先是数字所以那是m[1]
    • 由第三列为空{ 25, 25A, 25B }。因此，将它们推到顶部，但是对于25/3and 25/4order ASC（默认）
    • 最后，按 ASC 顺序的第二列排序，顶部为 NULL。
    • 2

  3. 这行吗？（我生成了自己的测试数据，以便更容易确认'4'之前的数据'44'等。）

    with street_numbers (hnr) as
     ( select n1.column_value||n2.column_value
       from   table(sys.ku$_vcnt('4','40','44')) n1
              cross join table(sys.ku$_vcnt('', 'A','B','/1','/11')) n2 )
select hnr
from   street_numbers
order by
       regexp_substr(hnr,'^\d+')
     , replace(hnr,'/',chr(123));

    这使：

    HNR
-----
4
4A
4B
4/1
4/11
40
40A
40B
40/1
40/11
44
44A
44B
44/1
44/11

    这个想法是首先按前导数字排序，因此 4 在 5 之前，以此类推，然后按整个字符串，但将 any 更改/为上面的任意字符z。

    • 1

相关问题