乞援一查询sql

求助一查询sql表结构及内容如下：IDPIDDIDKEYVALUE425 applygoods.180001 90001 sys_tel139988*****426 app

求助一查询sql
表结构及内容如下：

ID PID DID KEY VALUE
425 applygoods.180001 90001 sys_tel 139988*****
426 applygoods.180001 90001 sys_name 1000800168
427 applygoods.180001 90001 htm_sl 32
428 applygoods.180001 90001 sys_dept 0102360601
429 applygoods.180001 90001 bpm_sqlb 1
430 applygoods.180001 90001 owner 1000800168
431 applygoods.180001 90001 bpm_wplb 3
432 applygoods.180001 90001 htm_xh xcvsdfsdf
433 applygoods.180001 90001 bpm_jfly 7
434 applygoods.180001 90001 opinion 同意
435 applygoods.180001 90001 htm_textarea xvcsdf
358 applygoods.150005 90001 htm_bz zxvcb eraw xcva swfewrgbcdezcvbbnfd
359 applygoods.150005 90001 sys_tel 139988*****
360 applygoods.150005 90001 htm_sl 5
361 applygoods.150005 90001 sys_name 1000800168
362 applygoods.150005 90001 bpm_sqlb 1
363 applygoods.150005 90001 bpm_wplb 4
364 applygoods.150005 90001 bpm_jfly 7
365 applygoods.150005 90001 htm_xh cvb23xcvsdfazvcbxcbzxcv
366 applygoods.150005 90001 htm_textarea xcvas324
367 applygoods.150005 90001 sys_dept 0102360601
368 applygoods.150005 90001 owner 1000800168
369 applygoods.150005 90001 opinion 不同意
425 applygoods.200002 90005 sys_tel 139988*****
426 applygoods.200002 90005 sys_name 1000800168
427 applygoods.200002 90005 htm_sl 32
428 applygoods.200002 90005 sys_dept 0102360601
429 applygoods.200002 90005 bpm_sqlb 1
430 applygoods.200002 90005 owner 1000800168
431 applygoods.200002 90005 bpm_wplb 3
432 applygoods.200002 90005 htm_xh xcvsdfsdf
433 applygoods.200002 90005 bpm_jfly 7
434 applygoods.200002 90005 opinion 同意
435 applygoods.200002 90005 htm_textarea xvcsdf

查询的需求是pid和did相同的记录为一组，按key里的value值查询满足条件的pid和did。例如查询条件为bpm_sqlb=1,bpm_wplb=3和bpm_jfly=7，查询pid和did的结果为applygoods.180001,90001和applygoods.200002,90005两条记录

恳请各位帮忙看看有没有什么好的思路，先谢谢了

附建表语句及数据

SQL code

create table TEST(  ID    VARCHAR2(255 CHAR) not null,  PID   VARCHAR2(255 CHAR),  DID   VARCHAR2(255 CHAR),  KEY   VARCHAR2(255 CHAR),  VALUE VARCHAR2(2000 CHAR))

SQL code

 
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('425', 'applygoods.180001', '90001', 'sys_tel', '139988*****'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('426', 'applygoods.180001', '90001', 'sys_name', '1000800168'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('427', 'applygoods.180001', '90001', 'htm_sl', '32'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('428', 'applygoods.180001', '90001', 'sys_dept', '0102360601'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('429', 'applygoods.180001', '90001', 'bpm_sqlb', '1'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('430', 'applygoods.180001', '90001', 'owner', '1000800168'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('431', 'applygoods.180001', '90001', 'bpm_wplb', '3'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('432', 'applygoods.180001', '90001', 'htm_xh', 'xcvsdfsdf');  
 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('433', 'applygoods.180001', '90001', 'bpm_jfly', '7'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('434', 'applygoods.180001', '90001', 'opinion', '同意'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('435', 'applygoods.180001', '90001', 'htm_textarea', 'xvcsdf'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('358', 'applygoods.150005', '90001', 'htm_bz', 'zxvcb eraw xcva  swfewrgbcdezcvbbnfd'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('359', 'applygoods.150005', '90001', 'sys_tel', '139988*****'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('360', 'applygoods.150005', '90001', 'htm_sl', '5'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('361', 'applygoods.150005', '90001', 'sys_name', '1000800168'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('362', 'applygoods.150005', '90001', 'bpm_sqlb', '1'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('363', 'applygoods.150005', '90001', 'bpm_wplb', '4'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('364', 'applygoods.150005', '90001', 'bpm_jfly', '7'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('365', 'applygoods.150005', '90001', 'htm_xh', 'xcvb23xcvsdfazvcbxcbzxcv'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('366', 'applygoods.150005', '90001', 'htm_textarea', 'xcvas324'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('367', 'applygoods.150005', '90001', 'sys_dept', '0102360601'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('368', 'applygoods.150005', '90001', 'owner', '1000800168'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('369', 'applygoods.150005', '90001', 'opinion', '不同意'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('425', 'applygoods.200002', '90005', 'sys_tel', '139988*****'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('426', 'applygoods.200002', '90005', 'sys_name', '1000800168'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('427', 'applygoods.200002', '90005', 'htm_sl', '32'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('428', 'applygoods.200002', '90005', 'sys_dept', '0102360601'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('429', 'applygoods.200002', '90005', 'bpm_sqlb', '1'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('430', 'applygoods.200002', '90005', 'owner', '1000800168'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('431', 'applygoods.200002', '90005', 'bpm_wplb', '3'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('432', 'applygoods.200002', '90005', 'htm_xh', 'xcvsdfsdf'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('433', 'applygoods.200002', '90005', 'bpm_jfly', '7');  
 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('434', 'applygoods.200002', '90005', 'opinion', '同意'); 
  
 insert into test (ID, PID, DID, KEY, VALUE) 
 values ('435', 'applygoods.200002', '90005', 'htm_textarea', 'xvcsdf');

[解决办法]
查询的需求是pid和did相同的记录为一组，按key里的value值查询满足条件的pid和did，
你想要的结果最好用例子表示出来，这样比较明了
[解决办法]

SQL code

SELECT distinct pid,didFROM TEST tWHERE EXISTS(  SELECT 1 FROM TEST WHERE pid=t.pid AND did=t.did  AND KEY='bpm_sqlb' AND VALUE='1')and EXISTS(  SELECT 1 FROM TEST WHERE pid=t.pid AND did=t.did  AND KEY='bpm_wplb' AND VALUE='3')and EXISTS(  SELECT 1 FROM TEST WHERE pid=t.pid AND did=t.did  and key='bpm_jfly' and value='7')
[解决办法]
SQL codeSELECT pid,didfrom testGROUP BY pid,didHAVING count(CASE WHEN KEY='bpm_sqlb' AND VALUE='1' OR  KEY='bpm_wplb' AND VALUE='3' OR  key='bpm_jfly' and value='7' then 1 end)=3
[解决办法]
SQL codeSELECT pid, did  FROM test t WHERE (t.key = 'bpm_sqlb' AND VALUE = '1') OR       (t.key = 'bpm_wplb' AND VALUE = '3') OR       (t.key = 'bpm_jfly' AND VALUE = '7') GROUP BY pid, didHAVING COUNT(1) >= 3;
[解决办法]
如果是固3个条件，这样写就好了：

SQL codeSQL> select a.pid, a.did  2    from test a, test b, test c  3   where a.key = 'bpm_sqlb' and a.value = '1'  4     and b.key = 'bpm_jfly' and b.value = '7'  5     and c.key = 'bpm_wplb' and c.value = '3'  6     and a.pid = b.pid and a.did = b.did  7     and a.pid = c.pid and a.did = c.did  8     ; PID                                                                              DID-------------------------------------------- --------------------------------------------applygoods.180001                                                                90001applygoods.200002                                                                90005
[解决办法]
条件不固定的话，可以将6楼的改成动态sql就可以了