如何实现子查询与主查询同维度分组？解决ORA-01427报错

问题背景 #

现有SQL主查询用于统计各门诊-医生维度的出院人数，同时原本通过子查询统计全量就诊总人数。现在需要将子查询改为按clinic_no、doctor_no分组，统计对应维度的总就诊人数，但直接修改子查询添加group by后触发ORA-01427错误。

原主查询代码：

select a.clinic_no , 
b.CLINIC_DESC_A , 
a.doctor_no , 
c.STAFF_NATIVE_NAME , 
count(DISCHARGE_FROM_CLINIC) , 
(select count(patient_no) from trng.opd_visits_history WHERE event_date BETWEEN 20240815 and 20240822) as "Total"
from trng.opd_visits_history a , trng.hospital_clinics b  , trng.hospital_staff c 
WHERE event_date BETWEEN 20240815 and 20240822
 and a.CLINIC_NO = b.CLINIC_NO
and a.DOCTOR_NO = c.STAFF_NO 
and b.DOCTOR_NO = c.STAFF_NO
and a.HOSPITAL_NO = 720022
and b.HOSPITAL_NO = 720022
and c.HOSPITAL_NO = 720022  
AND a.DISCHARGE_FROM_CLINIC = 1
group by a.clinic_no , a.doctor_no , b.CLINIC_DESC_A , c.STAFF_NATIVE_NAME

期望修改后的子查询（直接添加group by导致错误）：

select count(patient_no) from trng.opd_visits_history WHERE event_date BETWEEN 20240815 and 20240822 group by clinic_no , doctor_no

错误原因 #

ORA-01427错误的核心是：单行子查询要求返回且仅返回1条结果，但修改后的子查询按clinic_no、doctor_no分组后会返回多条记录，无法直接作为主查询中每行的单个字段值。

解决方案 #

方法1：关联子查询添加匹配条件 #

将子查询与主查询的clinic_no、doctor_no进行关联，确保子查询仅返回当前行对应维度的统计值：

select a.clinic_no , 
b.CLINIC_DESC_A , 
a.doctor_no , 
c.STAFF_NATIVE_NAME , 
count(DISCHARGE_FROM_CLINIC) as discharge_count,
-- 关联子查询，匹配当前行的clinic_no和doctor_no
(select count(patient_no) 
 from trng.opd_visits_history t
 WHERE t.event_date BETWEEN 20240815 and 20240822
   and t.clinic_no = a.clinic_no
   and t.doctor_no = a.doctor_no) as "Total"
from trng.opd_visits_history a 
join trng.hospital_clinics b on a.CLINIC_NO = b.CLINIC_NO and a.HOSPITAL_NO = b.HOSPITAL_NO
join trng.hospital_staff c on a.DOCTOR_NO = c.STAFF_NO and a.HOSPITAL_NO = c.HOSPITAL_NO
WHERE a.event_date BETWEEN 20240815 and 20240822
  AND a.DISCHARGE_FROM_CLINIC = 1
  AND a.HOSPITAL_NO = 720022
group by a.clinic_no , a.doctor_no , b.CLINIC_DESC_A , c.STAFF_NATIVE_NAME

方法2：用CTE预统计总就诊人数，再关联主查询 #

先通过CTE预计算每个门诊-医生维度的总就诊数，再和主查询的统计结果关联，性能更优：

with total_visits as (
    select clinic_no, doctor_no, count(patient_no) as total_count
    from trng.opd_visits_history
    where event_date BETWEEN 20240815 and 20240822
      and hospital_no = 720022 -- 提前过滤医院，减少计算量
    group by clinic_no, doctor_no
)
select a.clinic_no , 
b.CLINIC_DESC_A , 
a.doctor_no , 
c.STAFF_NATIVE_NAME , 
count(DISCHARGE_FROM_CLINIC) as discharge_count,
nvl(t.total_count, 0) as "Total" -- 用nvl处理无就诊记录的维度
from trng.opd_visits_history a 
join trng.hospital_clinics b on a.CLINIC_NO = b.CLINIC_NO and a.HOSPITAL_NO = b.HOSPITAL_NO
join trng.hospital_staff c on a.DOCTOR_NO = c.STAFF_NO and a.HOSPITAL_NO = c.HOSPITAL_NO
left join total_visits t on a.clinic_no = t.clinic_no and a.doctor_no = t.doctor_no
WHERE a.event_date BETWEEN 20240815 and 20240822
  AND a.DISCHARGE_FROM_CLINIC = 1
  AND a.HOSPITAL_NO = 720022
group by a.clinic_no , a.doctor_no , b.CLINIC_DESC_A , c.STAFF_NATIVE_NAME, t.total_count

方法3：用窗口函数直接计算 #

利用窗口函数在主查询中直接计算对应维度的总就诊数，简化代码：

select distinct
a.clinic_no , 
b.CLINIC_DESC_A , 
a.doctor_no , 
c.STAFF_NATIVE_NAME ,
count(DISCHARGE_FROM_CLINIC) over(partition by a.clinic_no, a.doctor_no) as discharge_count,
count(patient_no) over(partition by a.clinic_no, a.doctor_no) as "Total"
from trng.opd_visits_history a 
join trng.hospital_clinics b on a.CLINIC_NO = b.CLINIC_NO and a.HOSPITAL_NO = b.HOSPITAL_NO
join trng.hospital_staff c on a.DOCTOR_NO = c.STAFF_NO and a.HOSPITAL_NO = c.HOSPITAL_NO
WHERE a.event_date BETWEEN 20240815 and 20240822
  AND a.HOSPITAL_NO = 720022
  AND a.DISCHARGE_FROM_CLINIC = 1

方案说明 #

• 方法1逻辑直观，适合数据量较小的场景；
• 方法2通过预统计减少重复计算，更适合大数据量场景；
• 方法3用窗口函数避免子查询或关联，代码更简洁，需注意distinct的使用以避免重复行。

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