如何查询出现至少3次的measr_comp_id并解决ORA-00936错误

报错原因 #

原SQL抛出ORA-00936: missing expression的核心原因是WHERE子句末尾的子查询没有关联外层逻辑，也没有对应比较运算符，不符合SQL语法要求。同时子查询的表别名和外层的d1_init_msrmt_data别名都叫imd，会出现命名冲突导致逻辑错误。

正确修改方案 #

方案1：IN子查询实现（逻辑直观，易理解） #

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 >= TO_DATE('01-MAY-21', 'DD-MON-RR', 'NLS_DATE_LANGUAGE=AMERICAN')
-- 新增符合要求的过滤条件
and imd.measr_comp_id IN (
    select imd2.measr_comp_id
    from d1_init_msrmt_data imd2
    group by imd2.measr_comp_id
    HAVING COUNT(*) >= 3
);

调整说明：

• 把原末尾非法的子查询改为imd.measr_comp_id IN (...)结构，子查询先筛选出所有出现次数≥3的measr_comp_id列表
• 子查询内表别名改为imd2，避免和外层表命名冲突
• 新增TO_DATE指定日期格式，避免会话日期语言/格式不匹配导致的查询异常
• 计数条件调整为>=3，匹配“出现次数不少于3次”的需求

方案2：分析函数实现（性能更优，适合大数据量场景） #

select * from (
    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,
    -- 按measr_comp_id分组统计出现次数
    COUNT(*) OVER(PARTITION BY imd.measr_comp_id) as comp_cnt
    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 >= TO_DATE('01-MAY-21', 'DD-MON-RR', 'NLS_DATE_LANGUAGE=AMERICAN')
) t where t.comp_cnt >=3;

调整说明：

用分析函数按measr_comp_id分组统计次数，无需二次扫描d1_init_msrmt_data表，执行效率更高，适合数据量较大的场景。

内容的提问来源于stack exchange，提问作者The_Analyst