SQL中的接连

来源：互联网发布：淘宝代购dw表是真的吗编辑：程序博客网时间：2024/05/16 15:30

问题：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 numFROM tb_function AS aLEFT JOIN tb_use_detail AS b ON a.id_fun = b.id_funWHERE a.deleted=0AND b.deleted=0GROUP BY b.id_fun

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

SQL改为

SELECT a. * , COUNT( b.id_detail ) AS numFROM tb_function AS aLEFT JOIN tb_use_detail AS b ON a.id_fun = b.id_funWHERE a.deleted=0GROUP BY b.id_fun

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

解决：

SELECT a. * , c.countFROM tb_function AS aLEFT JOIN (SELECT b.id_fun, b.deleted, COUNT( b.id_detail ) countFROM tb_use_detail AS bWHERE b.deleted =0GROUP BY b.id_fun) AS c ON a.id_fun = c.id_funWHERE a.deleted =0