时间戳

首页 > 解决方案 > 找出重叠 er 数据库中的总人数

sql - 找出重叠 er 数据库中的总人数

问题描述

我试图找到：

我似乎无法得到正确的答案。

SQL：

CREATE TABLE persons (
  id integer primary key,
  name text
);

CREATE TABLE doctors (
  id integer primary key,
  type text,
  FOREIGN KEY (id) REFERENCES persons(id)
);

CREATE TABLE patients (
  id integer primary key,
  suffering_from text,
  FOREIGN KEY (id) REFERENCES persons(id)
);

INSERT INTO persons (id, name) VALUES
(1, 'bob'), (2, 'james'), (3, 'bill'), (4, 'mark'), (5, 'chloe');

INSERT INTO doctors (id, type) VALUES
(2, 'family doctor'), (3, 'eye doctor'), (5, 'family doctor');

INSERT INTO patients (id, suffering_from) VALUES
(1, 'flu'), (2, 'diabetes');

选择语句：

select count(d.id) as total_doctors, count(pa.id) as total_patients, count(d.id) + count(pa.id) as both_doctor_and_patient
from persons p
JOIN doctors d
ON p.id = d.id
JOIN patients pa
ON p.id = pa.id;

http://www.sqlfiddle.com/#!17/98ae9/2

标签： sqlpostgresqlcountleft-joinaggregate-functions

解决方案

一种选择使用left joins frompersons和条件聚合：

select 
    count(dr.id) filter(where pa.id is null) cnt_doctor_not_patient,
    count(pa.id) filter(where dr.id is null) cnt_patient_not_doctor,
    count(pa.id) filter(where do.id is not null) cnt_patient_and_doctor,
    count(*)     filter(where dr.id is null and pa.id is null) cnt_persons_not_dotor_nor_patient
from persons pe
left join doctors  dr on dr.id = pe.id
left join patients pa on pa.id = pe.id

作为奖励，这使您有机会计算既不是病人也不是医生的人。如果您不需要该信息，则 afull join更简单，并且不需要带persons表：

select 
    count(dr.id) filter(where pa.id is null) cnt_doctor_not_patient,
    count(pa.id) filter(where dr.id is null) cnt_patient_not_doctor,
    count(pa.id) filter(where dr.id is not null) cnt_patient_and_doctor
from doctors dr 
full join patients pa using (id)

推荐阅读