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

子查询

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