为什么此 oracle 查询返回空结果集?

标签: oracle11g sql Oracle
发布时间: 2017/4/15 18:53:59
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有两个数据库具有一些演示数据的表像如下所示

Create table demo(uuid int, addressname varchar(50));

insert into demo values(1, 'intersportprofi');
insert into demo values(2, 'intersportprofi');
insert into demo values(3, 'intersportprofi');
insert into demo values(4, 'intersportmarket');
insert into demo values(5, 'intersportmarket');
insert into demo values(6, 'intersportmarket');

create table demo_av(uuid int, testid int, name varchar(50), value varchar(50));

insert into demo_av values(1, 1, 'sport','football basketball cricket');
insert into demo_av values(2, 1, 'brand','reebok  addidas nike');
insert into demo_av values(3, 2, 'sport','football basketball ');
insert into demo_av values(4, 2, 'brand','reebok  addidas ');

我写了下面的查询得到的结果,从这些表,但 oracle 返回空结果集。

SELECT  d.addressname  FROM demo d, demo_av dv
WHERE  d.uuid = dv.testid  AND d.addressname='intersportprofi' 
AND REGEXP_LIKE( dv.value, 'reebok') AND REGEXP_LIKE( dv.value, 'cricket') 

为什么呢?我哪里做错了吗?任何帮助将大大感激

解决方法 1:

基于您的意见,我想你会搜索到多个行具有相同的查询 testid 。这可以使用联接或像这样︰

SELECT DISTINCT d.addressname  
FROM demo AS d
WHERE d.addressname = 'intersportprofi' 
  AND EXISTS
      ( SELECT *
        FROM demo_av AS dv
        WHERE d.uuid = dv.testid 
          AND dv.value LIKE '%reebok%'
      )
  AND EXISTS
      ( SELECT *
        FROM demo_av AS dv
        WHERE d.uuid = dv.testid 
          AND dv.value LIKE '%cricket%'
      ) ;
官方微信
官方QQ群
31647020