sql 查询left join项的个数

October 11, 2019

实际使用场景: 某一个流程被督办了，需要使用 sql 查询该流程的 id 在督办表里出现的次数。

很棒的回答: stackoverflow : sql - left join - count

sql 形似:

SELECT  
       a.ArticleID, 
       a.Article, 
       isnull(c.Cnt, 0) as Cnt 
FROM Article a 
LEFT JOIN 
    (SELECT c.ArticleID, count(1) Cnt
     FROM Comment c
    GROUP BY c.ArticleID) as c
ON c.ArticleID=a.ArticleID 
ORDER BY 1

根据业务将sql修改为，大概长这样:

SELECT bas.*, category.category_name, cc.cnt AS supervised_count FROM "be_approved_supervise"   as  bas       
     LEFT JOIN
     (
     SELECT task_id, count(1) as cnt FROM be_supervise  GROUP BY be_supervise.task_id
     ) as  cc ON cc.task_id = bas.id

     WHERE bas.id = #{id}

通过 group by 统计督办表中任务id的个数，进行left join，最终查询出的个数可能为null或者为n(n为出现的个数)。