SQL,它在给定的起点和终点之间生成字母序列

我需要一个SQL,它在给定的起点和终点之间生成字母序列 .

喜欢,对于 Start='C' End='G' 输出应该是

C
D
E
F
G

回答(3)

2 years ago

select chr(ascii('C') + level - 1) 
from dual
connect by ascii('C') + level - 1 <= ascii('G');

像这样使用 connect by (没有 start with 和只取决于级别的结束条件)是未记录的(并且不受支持),因此它可能会中断任何时间(尽管我不知道任何不适用的版本) .

从11.2开始,您还可以使用递归公用表表达式:

with letters (letter, inc) as (
   select 'C', 1 as inc
   from dual
   union all
   select chr(ascii('C') + p.inc), p.inc + 1
   from letters p
   where p.inc < 5
)
select letter
from letters;

2 years ago

这样的事情起作用了

WITH X AS
(SELECT 'C' as St, 'G' as En FROM dual)
SELECT CHR(ASCII(X.St)+ROWNUM-1)
FROM X
CONNECT BY ROWNUM<=(ASCII(X.En)-ASCII(X.St)+1)

2 years ago

修改了@a_horse_with_no_name,如下所示

WITH X(lettr) AS 
(SELECT 'C' lettr FROM DUAL
 UNION ALL
 SELECT CHR(ASCII(X.lettr)+1) letter
 FROM X 
 WHERE ASCII(X.lettr)<ASCII('G'))
 SELECT * FROM X