主页 / coding / 问题 / 77738338
Accepted
Ziad Adnan
Asked: 2023-12-31 14:00:10 +0800 CST

如何使用oracle SQL在一行中获取每天的查询结果？

  • 772

我需要在 oracle SQL 中进行 SELECT 并将每天的结果放在一行中

我尝试了以下选择

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

我该怎么做，请提前谢谢你

oracle

1 个回答

  1. Best Answer

    你没有发布测试用例，所以我简化了情况，只使用了一张表；自己加入，但这就是如何做到这一点。

    样本数据：

    SQL> with history (admission_date, nationality, patient_no) as
  2    (select '20230113', 1, 1 from dual union all
  3     select '20230113', 1, 2 from dual union all
  4     select '20230113', 2, 3 from dual union all
  5     select '20230113', 3, 4 from dual union all
  6     --
  7     select '20230114', 1, 1 from dual union all
  8     select '20230114', 3, 4 from dual union all
  9     select '20230114', 4, 5 from dual
 10    )

    查询：条件聚合：

     11  select admission_date,
 12    substr(admission_date, 7, 2) as admission_day,
 13    sum(case when nationality = 1 then 1 else 0 end) as s,
 14    sum(case when nationality > 1 then 1 else 0 end) as ns
 15  from history
 16  group by admission_date;

ADMISSION_DATE  ADMISSION_DAY            S         NS
--------------- --------------- ---------- ----------
20230113        13                       2          2
20230114        14                       1          2

SQL>

    还有一件事：为什么要将入场日期作为字符串保留在 VARCHAR2（或类似）列中？这就需要出现问题。如果可能，请切换到DATE数据类型。

    • 2

相关问题