Oracle SQL:使用分隔字符串对行进行排序 [英] Oracle SQL : sort row with delimited string
本文介绍了Oracle SQL:使用分隔字符串对行进行排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的SQL查询(在Oracle 11g中)返回这样的列:
My SQL query (in Oracle 11g) returns a column like this:
31132,11100,44232
25464,89453,15686
21561,68547,51612
我要订购这些单元格,我的意思是:
I want to order these cells, I mean:
11100,31132,44232
15686,25464,89453
21561,51612,68547
我该怎么做?
提前谢谢!
推荐答案
您可以使用REGEXP_SUBSTR从每一行中提取单个数字,然后使用LISTAGG以所需的顺序重新加入它们-类似于以下内容:
You can use REGEXP_SUBSTR to pull out the individual numbers from each row, and then use LISTAGG to rejoin them in the desired order -- something like the below:
WITH
T (N) AS --TEST DATA
(SELECT '31132,11100,44232' FROM DUAL
UNION
SELECT '25464,89453,15686' FROM DUAL
UNION
SELECT '21561,68547,51612' FROM DUAL),
T1 (N1) AS --USE THIS BELOW TO SEPARATE OUT FIRST/SECOND/THIRD VALUE FROM EACH ROW
(SELECT 1 FROM DUAL UNION SELECT 2 FROM DUAL UNION SELECT 3 FROM DUAL),
SEPARATED (RN,N) AS
(SELECT RN,REGEXP_SUBSTR(N,'[^,]+',1,T1.N1) N FROM --GET THE VALUES FROM EACH ROW WITH AN ACCOMPANYING ROW NUMBER SO WE CAN REGROUP THEM BELOW
(SELECT ROW_NUMBER() OVER(ORDER BY 1) RN ,N FROM T),T1)
SELECT LISTAGG(N,',') WITHIN GROUP (ORDER BY N) FROM SEPARATED GROUP BY RN; --USE LISTAGG TO REJOIN THE SEPARATED VALUES IN THE DESIRED ORDER
这篇关于Oracle SQL:使用分隔字符串对行进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文