时间戳

首页 > 解决方案 > Oracle 从多个数据库表中查找字符串匹配

sql - Oracle 从多个数据库表中查找字符串匹配

问题描述

这是一个有点复杂的问题来描述，但我会尝试用一个例子来解释它。我以为我可以使用 Oracle Instr 函数来完成此操作，但它不接受查询作为参数。

这是我的数据的简化：

  Table1
   Person         Qualities
   Joe            5,6,7,8,9
   Mary           7,8,10,15,20
   Bob            7,8,9,10,11,12

   Table2
   Id             Desc
   5              Nice
   6              Tall
   7              Short

   Table3
   Id             Desc
   8              Angry
   9              Sad
   10             Fun

   Table4
   Id             Desc
   11             Boring    
   12             Happy
   15             Cool
   20             Mad

这是一个查询，以了解我要完成的工作：

   select * from table1 
   where instr (Qualities, select Id from table2, 1,1) <> 0
   and instr (Qualities, select Id from table3, 1,1) <> 0 
   and instr (Qualities, select Id from table3, 1,1) <> 0

我试图从 3 组品质（表 2,3 和 4）中找出哪些人至少具有 1 种品质

因此，Joe 不会在结果中返回，因为他没有来自 3 个组中的每一个的质量，但 Mary 和 Joe 会因为他们从每个组中至少具有 1 个质量。

我们正在运行 Oracle 12，谢谢！

标签： sqloracleoracle12c

解决方案

这是一个选项：

SQL> with
  2  table1 (person, qualities) as
  3    (select 'Joe', '5,6,7,8,9' from dual union all
  4     select 'Mary', '7,8,10,15,20' from dual union all
  5     select 'Bob', '7,8,9,10,11,12' from dual
  6    ),
  7  table2 (id, descr) as
  8    (select 5, 'Nice' from dual union all
  9     select 6, 'Tall' from dual union all
 10     select 7, 'Short' from dual
 11    ),
 12  table3 (id, descr) as
 13    (select 8, 'Angry' from dual union all
 14     select 9, 'Sad' from dual union all
 15     select 10, 'Fun' from dual
 16    ),
 17  table4 (id, descr) as
 18    (select 11, 'Boring' from dual union all
 19     select 12, 'Happy' from dual union all
 20     select 15, 'Cool' from dual union all
 21     select 20, 'Mad' from dual
 22    ),
 23  t1new (person, id) as
 24    (select person, regexp_substr(qualities, '[^,]+', 1, column_value) id
 25     from table1 cross join table(cast(multiset(select level from dual
 26                                                connect by level <= regexp_count(qualities, ',') + 1
 27                                               ) as sys.odcinumberlist))
 28    )
 29  select a.person,
 30        count(b.id) bid,
 31        count(c.id) cid,
 32        count(d.id) did
 33  from t1new a left join table2 b on a.id = b.id
 34               left join table3 c on a.id = c.id
 35               left join table4 d on a.id = d.id
 36  group by a.person
 37  having (    count(b.id) > 0
 38          and count(c.id) > 0
 39          and count(d.id) > 0
 40         );

PERS        BID        CID        DID
---- ---------- ---------- ----------
Bob           1          3          2
Mary          1          2          2

SQL>

它有什么作用？

  • 第 1 - 22 行代表您的样本数据
  • T1NEWCTE（第 23 - 28 行）按每个人将逗号分隔的质量分成行
  • final select（第 29 - 40 行）是t1new与每个“描述”表 ( table2/3/4) 的外部连接，并计算每个人的品质中包含多少品质（由来自 的行表示t1new）
  • having条款在这里只返回所需的人；这些计数中的每一个都必须是正数

推荐阅读