如何用Oracle SQL实现每日查询结果单行展示？

原SQL语句：

select distinct admission_date ,
       SUBSTR(ADMISSION_DATE,7,2) as "admission day" , 
       case when nationality = 1 then count(distinct a.patient_no) end as "S" ,
       case when nationality > 1 then count(distinct a.patient_no) end as "NS" 
from inp_admission_history a , mr_files b
where admission_date between 20231201 and 20231230
and a.PATIENT_NO = b.PATIENT_NO 
group by admission_date ,  SUBSTR(ADMISSION_DATE,7,2) , nationality
order by admission_date

当前错误结果：

admission_date    admission day   S   NS 
20231201             01           15
20231201             01               20
20231201             01               7

期望结果：

admission_date    admission day   S   NS 
20231201             01           15  27 

问题核心是group by子句包含了nationality，导致每日数据按国籍拆分显示多行。调整方式为将条件判断嵌套进聚合函数内部，并移除分组维度中的国籍字段：

修改后的SQL：

select admission_date,
       SUBSTR(ADMISSION_DATE,7,2) as "admission day",
       count(distinct case when nationality = 1 then a.patient_no end) as "S",
       count(distinct case when nationality > 1 then a.patient_no end) as "NS"
from inp_admission_history a
join mr_files b on a.PATIENT_NO = b.PATIENT_NO
where admission_date between 20231201 and 20231230
group by admission_date, SUBSTR(ADMISSION_DATE,7,2)
order by admission_date

修改说明： #

• 将case条件判断移入count(distinct ...)内部，在每日分组下直接统计符合国籍条件的唯一患者数量
• 从group by中移除nationality，确保分组维度仅为日期，每日仅返回一行结果
• 将老式逗号连接表的写法改为显式join，提升SQL的可读性和维护性

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