sql - 如何在子查询中修复此错误,以便我可以区分

候选人table:

id           name             age   
------       --------        --------- 
 25           Taylor          30
 113          Paul            21
 10           Laura            19

报告table:

id           company        candidate_id      score
------       --------        -----------       ------ 
 1            Codility             10             20
 36              Soft             113             60
 137          Codility            10              30
 137          ITCompany           10              30

查询应该返回:

id           name             companies
------       --------        --------- 
 10           Laura            2 
 25           Taylor           0
 113           Paul            1

到目前为止,我能够使用此查询 SELECT DISTINCT r.candidate_id, r.company FROM reports r 从报告 table 中检索不同的候选人和公司,这为我提供了与不同公司相关联的候选人:

candiate_id   company
------       --------        
 113           Soft           
 10           Codility         
 10          ITCompany

现在,当我尝试使用 'candidates table 计算公司为每个 candiate_id 出现的次数并将上述选择查询用作子查询时,我收到一条错误消息。带有子查询的查询是:

SELECT r.candidate_id, COUNT(r.company) AS companies
FROM (SELECT DISTINCT r.candidate_id, r.company FROM reports r) as dr
GROUP BY r.candidate_id

有任何想法吗?

回答1

有两种方法: 将左连接的结果分组:

select c.id, c.name, 
       count(distinct r.company) as companies
from candidates c
  left join reports r on r.candidate_id = c.id
group by c.id, c.name;  
order by c.id

或者加入聚合的结果:

select c.id, c.name, 
       coalesce(r.companies, 0) as companies
from candidates c
  left join (
     select candidate_id, count(distinct company) as companies
     from reports
     group by candidate_id
  ) r on r.candidate_id = c.id
order by c.id;

这是有效的,因为左连接将为没有报告的候选人产生 null,因此 count() 将用零计算。

第二种方法有时比第一种更快。

相似文章