我需要以下regexp_split_table输出的输出:-
select regexp_split_to_table ('a,b,c,d,e,f',',') expr
---------------------------------------------
rownum expr
--------------
1 a
2 b
3 c
4 d
5 e
6 f
我也尝试过使用row_number((函数,但对于的所有行,它都返回1
select row_number() over (), regexp_split_to_table ('a,b,c,d,e,f',',')
rownum expr
--------------
1 a
1 b
1 c
1 d
1 e
1 f
这就是with ordinality
的作用:
select rownum, expr
from regexp_split_to_table ('a,b,c,d,e,f',',') with ordinality as t(expr, rownum)
rownum("ordinality"(是regexp_split_to_table
结果中该元素的"索引">
注意,将string_to_array()
与unnest
一起使用通常会更快,因为正则表达式非常昂贵:
select rownum, expr
from unnest(string_to_array('a,b,c,d,e,f',',')) with ordinality as t(expr, rownum)
尝试将当前查询包装在CTE中,然后使用ROW_NUMBER
:
WITH cte AS (
SELECT REGEXP_SPLIT_TO_TABLE ('a,b,c,d,e,f', ',') expr
)
SELECT
ROW_NUMBER() OVER (ORDER BY expr) rownum,
expr
FROM cte
ORDER BY expr;