oracle自联接多次查询进行条件过滤

Question

问题如下:
大家看，有没得解决这个问题，我简单说下哈，数据库的
一个表(假设为ta)有3个字段x_id,name,value
数据类似于如下
x_id,name,value
1,  姓名,李
1,  性别,男
2,  姓名,李
2,  性别,女
2,  年龄,12
3,  身高,168我现在有这么一个参数,类似多个条件
{[姓名:'李',性别:'女',年龄:12},条件的长度不限(可能更多,如身高:160等)
查询出满足这个条件的x_id,在上面的数据库,我们知道是2
但是用sql,如何实现,
然后顺便考虑下效率问题.可以用存储过程,数据库函数
我现在就是对数据库不熟哈,各位看看噻.最好是用oracle来实现
现在已知的是自联接关联查询,如select distinct x_id from ta a,ta b,ta c(条件更多,再加别名) where a.name=姓名 and t.value=李 and b.name=性别 and b.value=女 and c.name=年龄 and c.value=12 and a.x_id = b.x_id and b.x_id = c.x_id但是这个查询,的效率无法保证,而且还要根据查询条件的长度多少,动态生成查询语句.
希望有个更好的,谢谢了.

Answer 1

create or replace procedure pro(v_parm in varchar2(2000))
is
   -- flag to check whether exist record to fill these conditions
   v_flag   number := 0;
   -- return value of column x_id 
   v_xid    number := 0;
   v_xid_tm number := 0;
   -- get two parms every time
   v_tmp1   varchar2(20);
   v_tmp2   varchar2(20);
   v_tmp    varchar2(2000) := v_parm;begin   -- get 1st name and value from parm
   v_tmp1 := substr(v_tmp, 1, instr(v_tmp,':',1,1)-1); 
   v_tmp2 := substr(v_tmp, instr(v_tmp,':',1,1)+1, instr(v_tmp,',',1,1)-1-instr(v_tmp,':',1,1));   select x_id into v_xid from ta where name = v_tmp1 and value = v_tmp2;   -- remove 1st name and value from parm
   v_tmp := substr(v_tmp,instr(v_tmp,',',1,1)+1);   while instr(v_tmp,',',1,1) > 0 and v_flag = 0 loop
      v_tmp1 := substr(v_tmp, 1, instr(v_tmp,':',1,1)-1); 
      v_tmp2 := substr(v_tmp, instr(v_tmp,':',1,1)+1, instr(v_tmp,',',1,1)-1-instr(v_tmp,':',1,1));
      select x_id into v_xid_tm from ta where name = v_tmp1 and value = v_tmp2;      if v_xid_tm = v_xid then
         v_tmp := substr(v_tmp,instr(v_tmp,',',1,1)+1);
      else
         v_flag := 1;
      end if;   end loop;   if v_flag = 1 then
      dbms_output.put_line('No record found');
   else
      dbms_output.put_line('The x_id is :'||v_xid);
   end if;exception
   when no_data_found then
      dbms_output.put_line('No record found');
   when others then 
      raise;
end pro;
/未测试，主要是instr和substr的使用输入参数形式为
'姓名:李,性别:女,年龄:12'

Answer 2

to shiyiwan
我看了你的代码,可能我没有说清楚,前面那个instr和substr其实都问题不大的(由于我条件没有描述清楚),问题主要在过滤数据方面,我看了你的循环中的条件,你用查询出来的第一批数据和第二批数据进行比较,如果相同,再进行下一次循环.
不过这里面有个问题,就是你建的
select x_id into v_xid from ta where name = v_tmp1 and value = v_tmp2;
它查出来就不是一条记录,可能我数据没有给完全哈,我再把测试数据准备一下,如下
x_id,name,value 
1,  姓名,李 
1,  性别,男 
2,  姓名,李 
2,  性别,女 
2,  年龄,12 
3,  身高,168
3,  姓名,李
3,  性别,女
3,  年龄,12然后用同样一个条件,{[姓名:'李',性别:'女',年龄:12},我现在查询出来的结果应该是2和3了,而不是2了,也就是说,查询出来的数据,肯定不是只有一条,根据条件我过滤多少进行处理.关于这个条件,我想在存储过程中用这种表示更为准备
数组一[3](数据分别为姓名,性别,年龄)
数组二[3](数据分别为李,女,12)
所以,我不知道你中间的循环比较是否正确,但我简单的作了一个例子,结果出异常了我把我的测试写出来如下:
create or replace procedure pro
is
x_id number:=0;
x_id_tm number:=0;
begin
select x_id into x_id from xx where key='姓名' and value='李';select x_id into x_id_tm from xx where key='年龄' and value='12';
if x_id = x_id_tm then
dbms_output.put_line('ok!!!');
else
dbms_output.put_line('not ok');end if;
exception
   when no_data_found then
      dbms_output.put_line('No record found');
   when others then
      dbms_output.put_line('others!!!');end pro;这个简单例子就是把查询出来的数据作了一个if比较,结果出异常到when others then
      dbms_output.put_line('others!!!');
这一段了.我对存储过程不太懂哈.

Answer 3

#5这是因为第一个select返回了多个结果，所以不能简单用Into了。可以改成用两个cursor做。
1.在select之前先处理条件字符串，比如条件对有4对，先放在数组中。
  那么cursor c1: select x_id,count(*) cnt from ta group by x_id having count(*) = 4; 有结果，进入step 2,如无结果，跳出，查无记录
2.循环c1, 循环条件数组，
2.1.cursor c2: select x_id from ta where name = 'array[1]' and value = 'array[1]'; 遍历c2,只有有一条记录和c1.x_id相同，则算成功，若成功，进入第2.2步，若无结果，跳出条件数组循环，进入c1下一个循环
2.2.cursor c2: select x_id from ta where name = 'array[2]' and value = 'array[2]';
...
2.3.若遍历4次均有结果，则记录下该x_id于结果数组result_array，并进入c1下一个循环；
3.若有result_array中有记录，则输出记录，若无记录，则 查无记录。需要两个数组a,b；a用于存放处理后的条件对，b用于存放结果x_id；
两个cursor c1,c2；c1用于找出所有记录数与条件对相同的x_id；c2用于找出每对条件对对应的x_id。

Answer 4

with ta as(select 1 x_id,'姓名' name,'李' value from dual
  union all select 1,'性别','男' from dual
  union all select 2,'姓名','李' from dual
  union all select 2,'性别','女' from dual
  union all select 2,'年龄','12' from dual
  union all select 3,'身高','168' from dual
)select x_id,name,value from (
  select ta.*,count(1)over(partition by x_id)c
  from ta
  where name='姓名' and value='李'
    or name='性别' and value='女'
    or name='年龄' and value='12')
where c=3或
select x_id from (
  select x_id,count(1)c
  from ta
  where name='姓名' and value='李'
    or name='性别' and value='女'
    or name='年龄' and value='12'
  group by x_id)
where c=3条件写到where后，因为字段类型要一致，所以value字段类型都为varchar2吧
c=3后面这个数字是条件数。如果输入的条件不一定是完整的条件，可以将c=3改成c>=3需要用过程来做的话可以将语句放到过程里用游标

Answer 5

to shiyiwan
谢谢, 明白你的思路了.
这一步
2.2.cursor c2:select x_id from ta where name = 'array[2]' and value = 'array[2]';再加一个条件, and x_id = curc2.1.x_id 是不是更快一点.然后这一步
2.1.cursor c2: select x_id from ta where name = 'array[1]' and value = 'array[1]',再加条件 and exists(select 1 from c1的集 where c1.x_id = x_id)
是不是更高效一点还有,就是我想到一种比如java中的处理集合的方法,我根据4(举例)个条件查出不同的集合,然后从中做 交集运算,这种和你这种,哪个好些?我现在晓得这种思路了,就是sql写不出来...
还是先谢谢哈.结贴:)

Answer 6

to @wildwave
你这种方法,看起来最简单了
理解起来也容易
不晓得两个方法,哪个效果高哈.

Answer 7

如果你的数据有顺序，如果你的参数顺序一致，可以用何种方法直接比较SQL> with t as(
  2    select 1 uuid,1 x_id,'姓名' name, '李' value from dual
  3    union
  4    select 2 uuid,1 x_id,'性别' name, '男' value from dual
  5    union
  6    select 3 uuid,2 x_id,'姓名' name, '李' value from dual
  7    union
  8    select 4 uuid,2 x_id,'性别' name, '女' value from dual
  9    union
 10    select 5 uuid,2 x_id,'年龄' name, '12' value from dual
 11    union
 12    select 6 uuid,3 x_id,'身高' name, '168' value from dual
 13  )
 14  select x_id,wmsys.wm_concat(name||':'||value) from (
 15    select * from t order by uuid
 16  ) t
 17  group by x_id
 18  ;      X_ID WMSYS.WM_CONCAT(NAME||':'||VAL
---------- --------------------------------------------------------------------------------
         1 姓名:李,性别:男
         2 姓名:李,年龄:12,性别:女
         3 身高:168SQL> 

Answer 8

刚才说错了..就用c=3，只要同一x_id下的name和value不重复不会出现c>3的情况

Answer 9

前面的改进都很好的，2.2的改进省去了判断。做交集那个，sql写起来麻烦点，但是应该也可以做