oracle - 将 Oracle 的 BITAND 与多值参数一起使用

Question

试图找到一种能够过滤查询的优雅方法，使用BITAND其中要and编辑的值由多值参数提供。

测试数据：

WITH

patient as
(
  select 1 patient_id, 'foo' patient_name from dual
  union all
  select 2 patient_id, 'bar' patient_name from dual
  union all
  select 3 patient_id, 'baz' patient_name from dual
  union all
  select 4 patient_id, 'zoo' patient_name from dual

)

,
-- each organ is a power of 2
organ as 
(
  select 2 organ_id, 'Lung' organ_name from dual
  union all
  select 4 organ_id, 'Pancreas' organ_name from dual
  union all
  select 8 organ_id, 'Liver' organ_name from dual
  union all
  select 16 organ_id, 'Kidney' organ_name from dual
)

,
patient_organ as
(
  -- patient with a multi-organ transplant
  select 1 patient_id, 4 organ_id from dual
  union all
  select 1 patient_id, 16 organ_id from dual
  union all

  -- patient with a single-organ transplant
  select 2 patient_id, 4 organ_id from dual
  union all

  -- patient with a multi-organ transplant
  select 3 patient_id, 8 organ_id from dual
  union all
  select 3 patient_id, 16 organ_id from dual
  union all

  -- patient with a single-organ transplant
  select 4 patient_id, 2 organ_id from dual

)

这个查询：

select  p.patient_id, p.patient_name
        ,po.bits,po.organs
from    patient p
inner join (

  SELECT  patient_id, sum(organ_id) AS BITS
          ,listagg(organ, '; ') within group (order by organ_id) ORGANS
  FROM    (
    SELECT  patient_id, po.organ_id, o.organ_name || ' [' || o.organ_id || ']' organ
    FROM    patient_organ po
    INNER JOIN organ o ON po.organ_id = o.organ_id
   )
  GROUP BY patient_id

) po on p.patient_id=po.patient_id

生成所需的数据集；显示多个器官（例如Pancreas [4]; Kidney [16]）：

PATIENT_ID, PATIENT_NAME, BITS, ORGANS
1   foo 20  Pancreas [4]; Kidney [16]
2   bar 4   Pancreas [4]
3   baz 24  Liver [8]; Kidney [16]
4   zoo 2   Lung [2]

我希望能够提供参数值4,8并获得以下结果：

PATIENT_ID, PATIENT_NAME, BITS, ORGANS
1   foo 20  Pancreas [4]; Kidney [16]
2   bar 4   Pancreas [4]
3   baz 24  Liver [8]; Kidney [16]

如果我有一个值（用:organ=4 模拟），我可以使用BITAND并获取多器官值：

select  p.patient_id, p.patient_name
        ,po.bits,po.organs
from    patient p
inner join (

  SELECT  patient_id, sum(organ_id) AS BITS
          ,listagg(organ, '; ') within group (order by organ_id) ORGANS
  FROM    (
    SELECT  patient_id, po.organ_id, o.organ_name || ' [' || o.organ_id || ']' organ
    FROM    patient_organ po
    INNER JOIN organ o ON po.organ_id = o.organ_id
   )
  GROUP BY patient_id

) po on p.patient_id=po.patient_id
WHERE bitand(bits,:organ)=:organ

多器官保存：

PATIENT_ID, PATIENT_NAME, BITS, ORGANS
1   foo 20  Pancreas [4]; Kidney [16]
2   bar 4   Pancreas [4]

我可以使用多值参数（模拟&organs=4,8）：

select  p.patient_id, p.patient_name
        ,po.bits,po.organs
from    patient p
inner join (

  SELECT  patient_id, sum(organ_id) AS BITS
          ,listagg(organ, '; ') within group (order by organ_id) ORGANS
  FROM    (
    SELECT  patient_id, po.organ_id, o.organ_name || ' [' || o.organ_id || ']' organ
    FROM    patient_organ po
    INNER JOIN organ o ON po.organ_id = o.organ_id
    WHERE   po.organ_id IN (&organs)
   )
  GROUP BY patient_id

) po on p.patient_id=po.patient_id

但这会丢失多器官结果：

PATIENT_ID, PATIENT_NAME, BITS, ORGANS
1   foo 4   Pancreas [4]
2   bar 4   Pancreas [4]
3   baz 8   Liver [8]

理想情况下，我可以将BITAND函数与IN语句一起使用，但这在语法上无效。

还有另一种非程序方法吗？

** 编辑 **

为了澄清，我在报告工具（Crystal Reports）中引用了这个 SQL。该工具允许您选择一个或多个参数值：您看到了organ_name，但organ_id提供了 。此外，参数的值作为数组或逗号分隔的字符串（很难说哪个）提供，而不是总和为单个值（如答案和评论中所建议）。这种架构使这变得困难。

标签： oracle

Answer 1

如果我正确理解您的问题，您想查询您的汇总数据，以了解至少接受过器官移植列表中的一个的患者。您应该能够通过在第一次尝试中更改谓词来做到这一点：

WHERE bitand(bits,:organ)=:organ

至

WHERE bitand(bits,:organ) != 0

然后，您可以提供您感兴趣的器官的位掩码（例如，and 的位掩码是 and的位掩码， Pancreas [4]and will Kidney [16]be20的位掩码）。这将起作用，因为只要掩码中的一位与位匹配并且结果将非零，如果没有匹配位并且结果将为零。Pancreas [4]Liver [8]12

要使用多值参数，您只需将其转换为位掩码并将 :organ 绑定值替换为派生的位掩码，如下所示：

WHERE bitand(bits,(select sum(distinct organ_id) from organ where organ_id in (&organs))) !=0

在这种情况下，我像您一样模拟多值参数，并将其转换为针对器官表的标量值子查询中的位掩码。