一个关于数据库联接查询的问题,求高手解答

Question

    首先，表结构如下:
   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 75 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 79 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
            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)希望大神帮帮忙哦

Answer 1

(select * from table(splitstr(t.name,',')))你这部分查出来的值是不是永远都是6，7 啊。。

Answer 2

不是啊,这是根据t.name的值来确定的,比如说:t.name=1,2时返回的是1和2 

Answer 3

  1  with t as
  2  (select 1 id,'123,124' name ,'6,7' name2 from dual
  3  union all
  4  select 2 id,'124,125' name ,'7,8' name2 from dual
  5  union all
  6  select 3 id,'125,123' name ,'6,8' name2 from dual
  7  ),
  8   s as
  9  (
 10  select '123' id,'123' name from dual
 11  union all
 12  select '124' id,'124' name from dual
 13  union all
 14  select '125' id,'125' name from dual
 15  ),
 16   p as
 17  (
 18  select '6' id,'6' name from dual
 19  union all
 20  select '7' id,'7' name from dual
 21  union all
 22  select '8' id,'8' name from dual
 23  )
 24  select t.id, t.name,t.name2,tt.id as test1_id,ttt.id as test3_id from t t
 25  left join s tt on tt.id in  (select * from table(split(t.name,',')))
 26* left join p ttt on ttt.id in  (select  * from table(split(t.name2,',')))
14:09:26 TXW0755_006B@orcl> /        ID NAME    NAM TES T
---------- ------- --- --- -
         1 123,124 6,7 123 6
         1 123,124 6,7 123 7
         1 123,124 6,7 124 6
         1 123,124 6,7 124 7
         2 124,125 7,8 124 7
         2 124,125 7,8 124 8
         2 124,125 7,8 125 7
         2 124,125 7,8 125 8
         3 125,123 6,8 123 6
         3 125,123 6,8 123 8
         3 125,123 6,8 125 6
         3 125,123 6,8 125 8已选择12行。已用时间:  00: 00: 00.01看我的结果
你不是耍我们吧？

Answer 4

我又仔细的做了一遍怎么还是错误的输出结果呢？ 请问大神你的具体执行代码是什么啊？  能不能发个具体执行sql的脚本给我？ 不胜感激！

Answer 5

哦我知道了，你是用with as建的虚拟表么，那你用create table重新建表在试一试呢，我这用with as建表也没错，但用create table建那三个表在做查询就出错了...

Answer 6

建表和with .. as 是一样的效果啊
你是不是insert的时候敲错了啊？
刚试了 create table也是一样的结果：

Answer 7

找到原因了，你数据库是10g的，我的是11g的,解析规则可能不一样，看执行计划，
这是你的,使用
Merge Join连接的这是我的,使用Nested Loops连接的
Nested Loops可以在产生第一条记录时即开始返回数据，Merge Join不行

Answer 8

你真的很棒啊！ 能不能推荐点oracle深入学习的资料啊！