sql子查询加入分组

查理

我正在尝试从我们的数据库中获取我们的用户列表，以及与他们来自同一队列的人数-在这种情况下，它们被定义为来自同一时间同一所医学院。

medical_school_id存储在doctor_record表中graduation_dt也存储在doctor_record表中。

我设法使用子查询写出此查询，该查询执行一个select语句，计算每行中其他行的数量，但这要花很多时间。我的逻辑告诉我，我应该先运行一个简单的GROUP BY查询，然后再以某种方式将medical_school_id加入其中。

按查询分组如下

select count(ca.id) , cdr.medical_school_id, cdr.graduation_dt
from account ca
LEFT JOIN doctor cd on ca.id = cd.account_id
LEFT JOIN doctor_record cdr on cd.gmc_number = cdr.gmc_number
GROUP BY cdr.medical_school_id, cdr.graduation_dt

长选择查询是

select a.id, a.email , dr.medical_school_id,
                     (select count(ba.id) from account ba
                    LEFT JOIN doctor bd on ba.id = bd.account_id
                    LEFT JOIN doctor_record bdr on bd.gmc_number = bdr.gmc_number
                    WHERE bdr.medical_school_id = dr.medical_school_id AND bdr.graduation_dt = dr.graduation_dt) AS med_count,


from account a
LEFT JOIN doctor d on a.id = d.account_id
LEFT JOIN doctor_record dr on d.gmc_number = dr.gmc_number

如果您能将我推向正确的方向，那就太好了

戈登·利诺夫

我认为您只需要窗口函数：

select a.id, a.email, dr.medical_school_id, dr.graduation_dt,
       count(*) over (partition by dr.medical_school_id, dr.graduation_dt) as cohort_size
from account a left join
     doctor d
     on a.id = d.account_id left join
     doctor_record dr
     on d.gmc_number = dr.gmc_number;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。