一个关于数据库联接查询的问题,求高手解答
首先,表结构如下:
create table TEST
(
id NUMBER not null,
name NVARCHAR2(100),
name2 NVARCHAR2(100)
)
create table TEST1
(
id VARCHAR2(28),
name VARCHAR2(100)
)
create table TEST3
(
id VARCHAR2(28),
name VARCHAR2(100)
)
插入数据:
prompt Loading TEST...
insert into TEST (id, name, name2)
values (1, '123,124', '6,7');
insert into TEST (id, name, name2)
values (2, '124,125', '7,8');
insert into TEST (id, name, name2)
values (3, '125,123', '6,8');
commit;
prompt 3 records loaded
prompt Loading TEST1...
insert into TEST1 (id, name)
values ('123', '123');
insert into TEST1 (id, name)
values ('124', '124');
insert into TEST1 (id, name)
values ('125', '125');
commit;
prompt 3 records loaded
prompt Loading TEST3...
insert into TEST3 (id, name)
values ('6', '6');
insert into TEST3 (id, name)
values ('7', '7');
insert into TEST3 (id, name)
values ('8', '8');
commit;
执行查询:
select t.id, t.name,t.name2,tt.id as test1_id,ttt.id as test3_id from test t
left join test1 tt on tt.id in (select * from table(splitstr(t.name,',')))
left join test3 ttt on ttt.id in (select * from table(splitstr(t.name2,',')))
查询结果:
行号 ID NAME NAME2 test1_id test3_id
1 1 123,124 6,7 123 6
2 1 123,124 6,7 123 7
3 1 123,124 6,7 124 6
4 1 123,124 6,7 124 7
5 2 124,125 7,8 123 6
6 2 124,125 7,8 123 7
7 2 124,125 7,8 124 6
8 2 124,125 7,8 124 7
9 3 125,123 6,8 123 6
10 3 125,123 6,8 123 7
11 3 125,123 6,8 124 6
12 3 125,123 6,8 124 7
从结果可以看出test1_id和test3_id两条数据从第5行开始并没按期望连接,说明这个连接查询是不正确的,这是什么问题呢?何解???????
另外,字符串分割函数如下面所述:
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split
PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
&nbs