select uppagent.agent_no AGENT_NO, ISNULL(countsubagent,0) REFERRAL_AGENT_NUM, ISNULL(countsubcustomer,0) CUSTOMER_NUM
from AGENT_CORRELATION uppagent
LEFT JOIN (select UPPER_AGENT_NO AGENT,count(AGENT_NO) countsubagent from AGENT_CORRELATION where UPPER_AGENT_NO is not NULL group by UPPER_AGENT_NO) subagent
on uppagent.AGENT_NO=subagent.AGENT
LEFT JOIN (select UPPER_AGENT_NO,count(id) countsubcustomer from CUSTOMER_INFO where UPPER_AGENT_NO is not NULL group by UPPER_AGENT_NO) customer
on customer.UPPER_AGENT_NO=uppagent.AGENT_NO

这种效率会高点

1，通过where UPPER_AGENT_NO is not NULL等条件使得大表转为小表

2.  使用单表统计后的数据进行关联效率比多表关联后的数据进行统计效率要高

sql简单优化点滴