SQL中的接连

问题：tb_function表中有字段 id_fun/title/deleted字段

tb_detail表中有字段 id_detail/id_fun/deleted字段

一个id_fun对应多个id_detail

要得到以下结果

id_fun  title   count(id_detail)

   1      x           0

   2     xxx          2

  …     …          …

我写的SQL如下：

SELECT a. * , COUNT( b.id_detail ) AS num
FROM tb_function AS a
LEFT JOIN tb_use_detail AS b ON a.id_fun = b.id_fun
WHERE a.deleted=0
AND b.deleted=0
GROUP BY b.id_fun

执行结果中不能查找到count(id_detail)为0的结果

SQL改为

SELECT a. * , COUNT( b.id_detail ) AS num
FROM tb_function AS a
LEFT JOIN tb_use_detail AS b ON a.id_fun = b.id_fun
WHERE a.deleted=0
GROUP BY b.id_fun

结果就有count(id_detail)为0的结果

解决：

SELECT a. * , c.count
FROM tb_function AS a
LEFT JOIN (
SELECT b.id_fun, b.deleted, COUNT( b.id_detail ) count
FROM tb_use_detail AS b
WHERE b.deleted =0
GROUP BY b.id_fun
) AS c ON a.id_fun = c.id_fun
WHERE a.deleted =0