一个关于数据库联接查询的问题,求高手解答
首先，表结构如下:
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,',')))
查询结果:
行号IDNAME NAME2test1_id test3_id
11123,1246,71236
21123,1246,71237
31123,1246,71246
41123,1246,71247
52124,1257,81236
62124,1257,81237
72124,1257,81246
82124,1257,81247
93125,1236,81236
103125,1236,81237
113125,1236,81246
123125,1236,81247
从结果可以看出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
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END splitstr;
返回类型如下面:
CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000)
希望大神帮帮忙哦
[解决办法]