在oracle中串联时排序字符串 [英] Ordering the strings while concatenating in oracle
本文介绍了在oracle中串联时排序字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用collect函数来连接用于SQL查询的字符串.
I am using the collect function to concatenate strings for a sql query.
select id,
tab_to_string(CAST(COLLECT(a.level||' '||d.Number||':
'||to_char(nvl(de.eventDate,SYSDATE - 365 * 100))) AS t_varchar2_tab)) AS MyVar
from Mytable
groupby id
此查询的输出如下:
Id Myvar
1 level : 27-Jan-09,level : 27-Mar-08, level : 2-Apr-10
2 level : 7-Jun-06,level : 27-Dec-08, level : 2-Nov-08
3 level : 27-July-10,level : 27-Mar-06, level : 2-Apr-10
但是我希望"Myvar"值按连接字符串中的日期字段排序
But i want the "Myvar" value to be ordered by the date field within the concatenated string
因此对于Id = 1,输出应类似于
so for the Id = 1, the output should be like
level : 27-Mar-08, level : 27-Jan-09, level : 2-Apr-10
下面是tab_to_string函数的代码
Below is the code for tab_to_string function
CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
l_string VARCHAR2(32767);
BEGIN
FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
IF i != p_varchar2_tab.FIRST THEN
l_string := l_string || p_delimiter;
END IF;
l_string := l_string || p_varchar2_tab(i);
END LOOP;
RETURN l_string;
END tab_to_string;
我正在使用Oracle 10g.
I am using Oracle 10g.
谢谢 垃圾
推荐答案
要获取有序列表,有几种方法.最简单的是:
to get an ordered list, there's a few ways. the simplest is :
select id, str
from (select id,
wm_concat('level : ' || to_char(nvl(eventDate,SYSDATE - 365 * 100)))
over (partition by id order by eventdate) str,
row_number() over (partition by id order by eventdate desc) rn
from Mytable)
where rn = 1;
或者如果您使用的是"stragg"用户定义的汇总:
or if you're using the "stragg" user defined aggregate:
select id, str
from (select id,
string_agg('level : ' || to_char(nvl(eventDate,SYSDATE - 365 * 100)))
over (partition by id order by eventdate) str,
row_number() over (partition by id order by eventdate desc) rn
from Mytable)
where rn = 1;
例如
SQL> select id, str
2 from (select id,
3 string_agg('level : ' || to_char(nvl(eventDate,SYSDATE - 365 * 100)))
4 over (partition by id order by eventdate) str,
5 row_number() over (partition by id order by eventdate desc) rn
6 from Mytable)
7 where rn = 1;
ID STR
---------- ----------------------------------------------------------------------
1 level : 27-MAR-08,level : 27-JAN-09,level : 02-APR-10
2 level : 07-JUN-06,level : 02-NOV-08,level : 27-DEC-08
3 level : 27-MAR-06,level : 02-APR-10,level : 27-JUL-10
这篇关于在oracle中串联时排序字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文