SELECT parent.*, COUNT(node.CategoryID), COUNT(domain.PortfolioID)
FROM categories_new AS parent
LEFT JOIN categories_new AS node ON node.lft > parent.lft AND node.rgt < parent.rgt
LEFT JOIN portfolios as domain ON domain.CategoryID = parent.CategoryID
GROUP BY parent.CategoryNameCN
ORDER BY parent.lft语句如上
其中两个count字段统计两个LEFT进来的数据,但是两个COUNT数据之间有相互影响
不用嵌套SQL可以实现这样的语句吗?
FROM categories_new AS parent
LEFT JOIN categories_new AS node ON node.lft > parent.lft AND node.rgt < parent.rgt
LEFT JOIN portfolios as domain ON domain.CategoryID = parent.CategoryID
GROUP BY parent.CategoryNameCN
ORDER BY parent.lft语句如上
其中两个count字段统计两个LEFT进来的数据,但是两个COUNT数据之间有相互影响
不用嵌套SQL可以实现这样的语句吗?
SELECT parent.*, COUNT(node.CategoryID), COUNT(domain.PortfolioID)
FROM categories_new AS parent ORDER BY parent.lft
LEFT JOIN categories_new AS node ON node.lft > parent.lft AND node.rgt < parent.rgt
LEFT JOIN portfolios as domain ON domain.CategoryID = parent.CategoryID
GROUP BY parent.CategoryNameCN
难道SQL语句里面多COUNT就是会出问题的么?
在手册看SUM函数的时候发现一个DISTINCT关键字,用在COUNT里面,问题可以解决
但效率异常低,比嵌套起来写要低得多。。饿。。谢谢4楼,结了