如何根据与第三行的关联来计算行数?在下面的示例中,我得到两个计数的结果相同,这不是"cwt_packages"的数量,也不是"cwt_products"表的数量。
在下面找到我写的查询
SELECT *, COUNT(p1.pkg_id) pkg_count, COUNT(p2.prd_id) prd_count FROM cwt_companies
LEFT JOIN cwt_packages p1 ON p1.pkg_ins_cmp = ic_id
LEFT JOIN cwt_products p2 ON p2.prd_pkg = p1.pkg_id AND p1.pkg_ins_cmp = ic_id
在不知道表的结构的情况下编写查询有点困难,但像这样的东西应该起作用:
select *, count( pkg1.pkg_id ) as pkg_count,
( select count( prd.prd_id ) as prd_count
from cwt_companies c2
left join cwt_packages pkg2 on pkg2.pkg_ins_cmp = c2.ic_id
left join cwt_products prd on prd.prd_pkg = pkg2.pkg_id
where c1.ic_id = c2.ic_id ) as prd_count
from cwt_companies c1
left join cwt_packages pkg1 on pkg1.pkg_ins_cmp = c1.ic_id
group by c1.ic_id;