试图找到一种优雅的方式来过滤查询,使用
BITAND
价值观在哪里
and
ED由多值参数提供。
测试数据:
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),我可以使用
比特
得到多器官值:
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]
理想情况下,我可以使用
比特
函数
IN
语句,但这在语法上无效。
是否有其他非程序方法?
**编辑**
为了澄清,我在一个报告工具(Crystal Reports)中引用了这个SQL。该工具允许您选择一个或多个参数值:您看到
organ_name
但是
organ_id
供应。此外,参数的值以数组或逗号分隔的字符串的形式提供(很难说是哪个),而不是求和为单个值(如答案和注释中建议的那样)。这种架构正是造成这种困难的原因。
