时间戳

首页 > 解决方案 > 如何从多个列中搜索多个值？

dql - 如何从多个列中搜索多个值？

问题描述

我需要在我们的 Documentum/Oracle 数据库中搜索可能存在于多个列中的值列表。

用 OR 语句将它们全部列出似乎会使 dqMan 崩溃！必须有更好的方法来做到这一点......否则我将不得不一次搜索一列。

SELECT DISTINCT
  accession_no,
  object_name,
  title,
  subject,
  authors,
  keywords,
  resolution_label,
  owner_name,
  owner_permit,
  group_name,
  group_permit,
  world_permit,
  log_entry,
  acl_domain,
  acl_name,
  language_code,
  archive_notes,
  archive_status,
  pier_authors,
  pier_author_ids,
  compound_nos,
  identifiers,
  issued_on,
  pier_keywords,
  organization,
  preferred_terms,
  protocol_nos,
  source_database,
  pier_title,
  pier_doc_cnt,
  created_by,
  created_on,
  piera_item_type,
  piera_barcode,
  piera_container_no,
  piera_item_description,
  piera_location,
  piera_microfilm_location,
  piera_microfilm_no,
  piera_archive_site,
  pier_department,
  pier_viewurl,
  application_name,
  file_paths,
  alliance_names,
  dmr_content_fk,
FROM
  pier_record
WHERE
  archive_notes in  ('51156','51443','51762','51810','18030','50373','52453','51115','51651','50093','18220','18449','50048','51139','52179','52185','52439','52437','51439','51760','51756','51754','51457','51441','51437','51455','51435','51453','5144  5','50726','CCL1','BCL6','ERAP1','HUSH') OR
  compound_nos in   ('51156','51443','51762','51810','18030','50373','52453','51115','51651','50093','18220','18449','50048','51139','52179','52185','52439','52437','51439','51760','51756','51754','51457','51441','51437','51455','51435','51453','5144  5','50726','CCL1','BCL6','ERAP1','HUSH') OR
  identifiers in    ('51156','51443','51762','51810','18030','50373','52453','51115','51651','50093','18220','18449','50048','51139','52179','52185','52439','52437','51439','51760','51756','51754','51457','51441','51437','51455','51435','51453','5144  5','50726','CCL1','BCL6','ERAP1','HUSH') OR
  pier_keywords in  ('51156','51443','51762','51810','18030','50373','52453','51115','51651','50093','18220','18449','50048','51139','52179','52185','52439','52437','51439','51760','51756','51754','51457','51441','51437','51455','51435','51453','5144  5','50726','CCL1','BCL6','ERAP1','HUSH') OR
  organization in   ('51156','51443','51762','51810','18030','50373','52453','51115','51651','50093','18220','18449','50048','51139','52179','52185','52439','52437','51439','51760','51756','51754','51457','51441','51437','51455','51435','51453','5144  5','50726','CCL1','BCL6','ERAP1','HUSH') OR
  protocol_nos in   ('51156','51443','51762','51810','18030','50373','52453','51115','51651','50093','18220','18449','50048','51139','52179','52185','52439','52437','51439','51760','51756','51754','51457','51441','51437','51455','51435','51453','5144  5','50726','CCL1','BCL6','ERAP1','HUSH')
ENABLE
 (ROW_BASED)

标签： dql

解决方案

我不确定您是否可以将公共表表达式 (CTE) 与 DQL 一起使用，但如果您正在搜索具有相同值的多个字段，则此处可能有意义：

WITH cte_value_lookup (MyValue) AS (
    SELECT '51156' UNION ALL
    SELECT '51443' UNION ALL
    ...
    SELECT 'HUSH'
) 
SELECT DISTINCT
  accession_no,
  object_name,
  ...
  dmr_content_fk,
FROM
  pier_record
WHERE
  archive_notes in  (SELECT MyValue FROM cte_value_lookup) OR
  compound_nos in   (SELECT MyValue FROM cte_value_lookup) OR
  identifiers in    (SELECT MyValue FROM cte_value_lookup) OR
  pier_keywords in  (SELECT MyValue FROM cte_value_lookup) OR
  organization in   (SELECT MyValue FROM cte_value_lookup) OR
  protocol_nos in   (SELECT MyValue FROM cte_value_lookup)
ENABLE
 (ROW_BASED)

不确定这是否会加快速度，但需要研究一下。

另一种方法是在这些字段（archive_notes、compound_nos 等）上添加索引，但这可能并不理想。

推荐阅读