如何解析varchar2并使其成多行？ [英] How to parse varchar2 and make it into multiple rows?
问题描述
这是这个问题的后续问题：我如何在这种情况下需要更改我的sql以获得我想要的？
This is kind of a follow-up question of this one: How do I need to change my sql to get what I want in this case?
我有两个表如下：
Table 1
id id2 date
1 2 2015-01-10
2 5 2015-06-13
3 9 2015-09-05
4 10 2015-02-11
5 26 2015-01-10
6 65 2015-01-25
Table 2
id id2 data(varchar2)
1 2 A
2 5 A
3 9 A
4 10 B
5 26 B
6 65 B
表2中的数据是varchar2类型，其中包含 N 数字，由 ，其中 N 可以依赖于 id2 。例如， A 可能是这样的：
The data in Table 2 is of type varchar2 and contains N numbers in it separated by , where N could be dependent on id2. For example, A might be something like this:
1.0,1.1,1.2,1.3,1.4,1.5,2.6,2.7,2.8,2.9, ...(ommitted)..., 9.5,9.9
我想写一个查询，返回表2 中的唯一数据在表1中具有最大日期
I want to write a query that returns the unique data in Table 2 that has the maximum date in Table 1, which would be the following for the above table:
id2 date number
2 2015-01-10 1.0
2 2015-01-10 1.1
2 2015-01-10 1.2
2 2015-01-10 1.3
...
2 2015-01-10 9.5
2 2015-01-10 9.9
10 2015-02-11 ***
10 2015-02-11 ***
...
10 2015-02-11 ***
每个独特的 id2 在查询输出中显示 N 次。
Each unique id2 appears N times in the query output.
我可以根据Fuzzy的答案获得独特的 id2 s，如下：
I am able to get the unique id2s based on Fuzzy's answer as follows:
select * from (
select
t2.id2, t1.date, t2.data,
row_number() over (partition by t2.data order by t1.date desc) rn
from table1 t1
join table2 t2 on t1.id2 = t2.id2
) t where rn = 1;
但我不知道如何从那里继续。非常感谢。
But I don't know how to continue from there. Thanks a lot.
推荐答案
Oracle安装：
CREATE TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION split_String(
i_str IN VARCHAR2,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2_TABLE DETERMINISTIC
AS
p_result VARCHAR2_TABLE := VARCHAR2_TABLE();
p_start NUMBER(5) := 1;
p_end NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
END IF;
END IF;
RETURN p_result;
END;
/
CREATE TABLE Table1 ( id, id2, "date" ) AS
SELECT 1, 2, DATE '2015-01-10' FROM DUAL UNION ALL
SELECT 2, 5, DATE '2015-06-13' FROM DUAL UNION ALL
SELECT 3, 9, DATE '2015-09-05' FROM DUAL UNION ALL
SELECT 4, 10, DATE '2015-02-11' FROM DUAL UNION ALL
SELECT 5, 26, DATE '2015-01-10' FROM DUAL UNION ALL
SELECT 6, 65, DATE '2015-01-25' FROM DUAL;
CREATE TABLE Table2 ( id, id2, data ) AS
SELECT 1, 2, '1.0,1.1,1.2' FROM DUAL UNION ALL
SELECT 2, 5, '1.0,1.1,1.2' FROM DUAL UNION ALL
SELECT 3, 9, '1.0,1.1,1.2' FROM DUAL UNION ALL
SELECT 4, 10, '3.2,3.3,4.5,6.7' FROM DUAL UNION ALL
SELECT 5, 26, '3.2,3.3,4.5,6.7' FROM DUAL UNION ALL
SELECT 6, 65, '3.2,3.3,4.5,6.7' FROM DUAL;
查询：
SELECT t.id,
t.id2,
t."date",
d.column_value AS data
FROM (
SELECT MAX( t1.id ) KEEP ( DENSE_RANK LAST ORDER BY t1."date" ) AS id,
MAX( t1.id2 ) KEEP ( DENSE_RANK LAST ORDER BY t1."date" ) AS id2,
MAX( t1."date" ) AS "date",
t2.data
FROM Table1 t1
INNER JOIN
Table2 t2
ON ( t1.id = t2.id AND t1.id2 = t2.id2 )
GROUP BY t2.data
) t,
TABLE( SPLIT_STRING( t.data, ',' ) ) d;
输出：
ID ID2 date DATA
---------- ---------- ------------------- ----
3 9 2015-09-05 00:00:00 1.0
3 9 2015-09-05 00:00:00 1.1
3 9 2015-09-05 00:00:00 1.2
4 10 2015-02-11 00:00:00 3.2
4 10 2015-02-11 00:00:00 3.3
4 10 2015-02-11 00:00:00 4.5
4 10 2015-02-11 00:00:00 6.7
这篇关于如何解析varchar2并使其成多行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
