该sql语句需要用到左外连接,思路是先查询出所有人员的基础信息作为表AA,再查询出所有人员的统计总数、总金额信息作为表BB,通过表AA左外连接表BB。说的更直白一点就是,AA表作为基础信息查询表,BB表作为统计信息查询需要用到聚合函数的表。示例如下:
select AA.user_account,AA.user_name,AA.MARKETING_CHANNEL,AA.DEPT_NAME,AA.pro,VNL(BB.total,'0'),NVL(BB.num,'0') from(
(SELECT gs.USER_ID,gs.USER_ACCOUNT,gs.USER_NAME,np.MARKETING_CHANNEL,pd.DEPT_NAME,pdt.DEPT_NAME pro from GSUSER gs
INNER join NET_POINT np on gs.NET_POINT_CODE = np.NET_POINT_CODE
INNER join PARA_DEPT pd on gs.SALESBRANCHNO = pd.DEPT_ID
inner join PARA_DEPT pdt on pd.PARENT_ID = pdt.DEPT_ID
WHERE gs.salesbranchno like '62%') AA
left join
(SELECT p.USER_ID,sum(p.PREMIUM) total,count(0) num from policies p
group by p.USER_ID) BB
on AA.user_id = BB.user_id);