mysql一对多表结构,查询一的信息的同时统计多的数量

res_resource_catalog表对于res_info_item表是一对多, 查询res_resource_catalog信息的同时,统计res_info_item中该条res_resource_catalog数据对应的数量 GROUP BY rrc.id是必须有的,如果没有的话结果中将永远只有一条数据 COUNT(DISTINCT rii.id) AS itemCount中如果不加 DISTINCT 结果中 itemCount 和 applyCount 结果将相同,这是错误的, 但是如果只有一个count可以不加

SELECT

rrc.id,

rrc.resource_name,

COUNT(DISTINCT rii.id) AS itemCount,

COUNT(DISTINCT rra.id) AS applyCount

FROM

res_resource_catalog AS rrc

LEFT JOIN res_info_item rii ON rrc.id = rii.resource_id

LEFT JOIN res_resource_apply rra ON rrc.id = rra.resource_id

GROUP BY rrc.id

转载地址https://www.cnblogs.com/cailijuan/p/11416887.html