数据过多时候，子查询改成left join减少笛卡尔积-526互联

子查询

SELECT
	cn.portal_id AS portalId,
	count( id ) AS num 
FROM
	construction_package_wbs_node cn 
WHERE
	cn.delete_flag = 0 
	AND (
		cn.node_type = '单位工程' 
		OR cn.node_type = '分部工程' 
		OR cn.node_type = '分项工程' 
		OR cn.node_type = '单元工程' 
		OR cn.node_type = '子检验批' 
		OR cn.node_type = '检验批' 
	) 
	AND id NOT IN ( SELECT ref_parent_id FROM construction_package_wbs_node WHERE delete_flag = 0 AND node_type = '子检验批' ) 
GROUP BY
	cn.portal_id

　　当construction_package_wbs_node 这张表数据条数如果多大，笛卡尔积就会更大，所以查询非常慢可能存在运行不出来，改成如下方式就会非常快，并且不止查一种门户的数据

SELECT
	cn.portal_id AS portalId,
	count( id ) AS num 
FROM
	construction_package_wbs_node cn
	LEFT JOIN ( SELECT ref_parent_id, portal_id FROM construction_package_wbs_node WHERE delete_flag = 0 AND node_type = '子检验批' GROUP BY portal_id, ref_parent_id ) sq ON cn.id = sq.ref_parent_id 
	AND cn.portal_id = sq.portal_id 
WHERE
	cn.delete_flag = 0 
	AND (
		cn.node_type = '单位工程' 
		OR cn.node_type = '分部工程' 
		OR cn.node_type = '分项工程' 
		OR cn.node_type = '单元工程' 
		OR cn.node_type = '子检验批' 
		OR cn.node_type = '检验批' 
	) 
	AND sq.ref_parent_id IS NULL 
GROUP BY
	cn.portal_id

update join inner left