sql - 如何计算每个类别中存在但其他类别中不存在的项目数？

Question

我有一张表格，其中包含不同的医院 visit_types。它们是Inpatient、Outpatient、Emergency

我想知道仅在每个 visit_type 下出现但在其他 visit_types 下不出现的受试者数。在上面的例子中

Inpatient count - 4

Outpatient count -2

Emergency count - 3

我尝试了以下但不确定它是否准确？

SELECT count(DISTINCT PERSON_ID) FROM Visit WHERE PERSON_ID NOT IN 
(select distinct person_id from Visit where visit_type = 'Inpatient') 
AND VISIT_type = 'Outpatient';

SELECT count(DISTINCT PERSON_ID) FROM Visit WHERE PERSON_ID NOT IN 
(select distinct person_id from Visit where visit_type = 'Inpatient') 
AND VISIT_type = 'Emergency';

当我这样做时，它包括 Emergency 和 Outpatient 之间的共同主题？

如何正确获取计数？

Answer 1

CTE 为每个 person_id 返回所有类型:

with cte as (
  select person_id, 
    sum(case visit_type when 'Inpatient' then 1 else 0 end) Inpatient,
    sum(case visit_type when 'Outpatient' then 1 else 0 end) Outpatient,
    sum(case visit_type when 'Emergency' then 1 else 0 end) Emergency
  from Visit
  group by person_id
)
select 
  case 
    when Inpatient > 0 then 'Inpatient' 
    when Outpatient > 0 then 'Outpatient' 
    when Emergency > 0 then 'Emergency'
  end visit_type,
  count(*) counter
from cte
group by visit_type

参见 demo .
结果:

visit_type | counter
:--------- | ------:
Outpatient |       2
Emergency  |       3
Inpatient  |       4