sql - 如何查询结果中遗漏的数据

Question

所以我有以下信息

输出

Diabetic    Schools   studentcount
false        9010         180
true         9010           3
false        9012         245
true         9012           4

查询

Select s.diabetic as diabetic, sch.buildingid as Schools, 
       count(distinct s.studentnmr) as Studentcount
from student s
inner join studentschool ss.studentid = s.studentid
inner join school sch.id = ss.schoolid
order by sch.id

我要

Diabetic    addresse    studentcount    calculation
true           9010           3             1,64 %
true           9012           4             1,61 %

计算在哪里

( sum(diabetic=true)/sum(total number of students of the school) )*100

额外的提示还有另一个字段叫做

 diabeticdate 

它有一个糖尿病为真的日期。

我的问题

当我选择

select sum(Case when s.diabetic is null then 1 else 0 end) AS notD

除了记录糖尿病 - 真实状态，我显然一无所获

我该如何解决这个问题

注意:如果您有更好的问题标题，请提出建议!

Answer 1

您可以使用条件聚合来显示每个学校的一行及其糖尿病发病率:

select
  sch.buildingid as Schools, 
  count(distinct s.studentnmr) as Studentcount
  count(distinct case when s.diabetic then s.studentnmr end) as Diabeticcount,
  count(distinct case when s.diabetic then s.studentnmr end) /
    count(distinct s.studentnmr) * 100 as rate
from student s
inner join studentschool on ss.studentid = s.studentid
inner join school on sch.id = ss.schoolid
group by sch.buildingid
having count(distinct case when s.diabetic then s.studentnmr end) > 0
order by sch.buildingid;

如果您还希望看到没有糖尿病患者的学校，请删除 HAVING 子句。