sql子查询加入分组依据

Question

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

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

我已经设法使用一个子查询来写出这个查询，该子查询执行一个选择语句来计算每一行的其他人的数量，但这需要永远。我的逻辑告诉我，我应该先运行一个简单的 GROUP BY 查询，然后以某种方式将 medical_school_id 加入到该查询中。

group by查询如下

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

如果你能把我推向正确的方向那就太棒了

Answer 1

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

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;