枚举Oracle中按字母顺序排列的行 [英] Enumerate rows alphabetized in Oracle

问题描述

我想知道在oracle中是否有可能将行号(例如，我们可以使用ROW_NUMBER()来获取数字)替换为数字编号

I wonder if this is possible in oracle to replace row number (we can use ROW_NUMBER() for example to get a digit) into alfabetical numbering

让我们说得到类似的东西

Let's say to get something like

NO | Name | Surname
================
A  | John | Doe
B  | Will | Doe
C  | Jim  | Wonder

代替

NO | Name | Surname |
=================
1  | John | Doe
2  | Will | Doe
3  | Jim  | Wonder

我有一个想法，可以创建一个像"ABCDEFG"这样的变量并将行号转换为正确的SUBSTR，但这听起来有点不稳定

I have an idea to create a variable like "ABCDEFG" and convert row number into correct SUBSTR, but this sounds a little unstable

A-Z的临时解决方案是使用

Temporary solution for A-Z is to use

CHR((ROW_NUMBER() OVER (PARTITION BY SOMECOLUMN ORDER BY 1))+64)

推荐答案

我创建了将数字转换为字符的函数:

I created function that converts number to characters:

CREATE OR REPLACE FUNCTION num_to_char(p_number IN NUMBER)
RETURN VARCHAR2
IS
  v_tmp    NUMBER;
  v_result VARCHAR2(4000) := '';
BEGIN
  v_result := CHR(MOD(p_number - 1, 26) + 65);
  IF p_number > 26 THEN
    v_result := num_to_char(TRUNC((p_number-1)/26)) || v_result;
  END IF;
  RETURN v_result;
END num_to_char;
/

您可以在选择项中使用它:

You can use it in selects:

SELECT num_to_char(ROW_NUMBER() OVER (PARTITION BY dummy ORDER BY 1))
FROM dual
CONNECT BY LEVEL < 3000

1-A，2-B，...，25-Y，26-Z，27-AA，28-AB，...，703-AAA，704-AAB，...

1 - A, 2 - B, ... , 25 - Y, 26 - Z, 27 - AA, 28 - AB, ..., 703 - AAA, 704 - AAB, ...

这篇关于枚举Oracle中按字母顺序排列的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！