如何从大于一个值的子查询返回结果

Question

我试图找到至少出现3次的测量组件id。此时抛出Oracle错误"ORA-00936:缺少表达式"。我已经尝试放入一个现有的语句，以可能满足缺失的表达式错误，但无济于事。它要么返回太多值，要么根本不返回任何值。我如何修复它以带回我的测量组件列带回至少发生3次的值的结果。

select td.td_entry_id, td.complete_dttm, imd.init_msrmt_data_id, imd.measr_comp_id,
imd.bus_obj_cd, imd.bo_status_cd, imd.status_upd_dttm, rep.last_name FROM
ci_td_entry td,
ci_td_drlkey drill,
d1_init_msrmt_data imd,
sc_user rep
WHERE td.td_type_cd ='D1-IMDTD'
and td.entry_status_flg = 'C'
and imd.init_msrmt_data_id = drill.key_value
and td.td_entry_id = drill.td_entry_id
and imd.bo_status_cd in ('DISCARDED','REMOVED')
and td.complete_user_id = rep.user_id
and td.complete_dttm >= '01-MAY-21'
and (select count(*)
from d1_init_msrmt_data imd
group by imd.measr_comp_id
HAVING COUNT(*) > 3);

Answer 1

如果我的测量组件列带回至少出现3次的值，我如何带回结果?

我猜这就是你要问的吗?

SELECT
td.td_entry_id,
td.complete_dttm,
imd.init_msrmt_data_id,
imd.measr_comp_id,
imd.bus_obj_cd,
imd.bo_status_cd,
imd.status_upd_dttm,
rep.last_name
FROM
ci_td_entry                      td
INNER JOIN ci_td_drlkey       drill ON drill.td_entry_id = td.td_entry_id
INNER JOIN d1_init_msrmt_data   imd ON imd.init_msrmt_data_id = drill.key_value
INNER JOIN sc_user              rep ON rep.user_id = td.complete_user_id
WHERE
td.td_type_cd ='D1-IMDTD'
AND td.entry_status_flg = 'C'
AND imd.bo_status_cd in ('DISCARDED','REMOVED')
AND td.complete_dttm >= '01-MAY-21'
AND (
SELECT COUNT(*)
FROM d1_init_msrmt_data
WHERE measr_comp_id = imd.measr_comp_id
) >= 3
;

"相同的measr_comp_id在d1_init_msrmt_data中出现至少3次的所有记录。">

Answer 2

我的原始查询使用组by和having函数，一旦您添加了表达式exists。要使子查询实际工作，需要做的是将其与主查询关联起来。这是通过添加在主查询中找到的日期参数来完成的，该参数查找"丢弃和删除"。还可以在子查询本身中创建内部连接，将子查询中使用的表连接到主查询中的表。最终的结果如下所示:

SELECT
td.td_entry_id,
td.complete_dttm,
imd.init_msrmt_data_id,
imd.measr_comp_id,
imd.bus_obj_cd,
imd.bo_status_cd,
imd.status_upd_dttm,
rep.last_name
FROM
ci_td_entry          td,
ci_td_drlkey         drill,
d1_init_msrmt_data   imd,
sc_user              rep
WHERE
td.td_type_cd = 'D1-IMDTD'
AND td.entry_status_flg = 'C'
AND imd.init_msrmt_data_id = drill.key_value
AND td.td_entry_id = drill.td_entry_id
AND td.complete_dttm = imd.status_upd_dttm
AND imd.bo_status_cd IN (
'DISCARDED',
'REMOVE'
)
AND td.complete_user_id = rep.user_id
AND EXISTS (
SELECT
COUNT(*)
FROM
d1_init_msrmt_data imd2
WHERE
imd.measr_comp_id = imd2.measr_comp_id
AND imd2.status_upd_dttm >= '01-JUN-21'
AND imd2.bo_status_cd IN (
'DISCARDED',
'REMOVE'
)
GROUP BY
imd2.measr_comp_id
HAVING
COUNT(*) >= 3
);

我感谢每个人的帮助才得到这个结果。