select count(*) from (SELECT A.* FROM (SELECT CD.*, nvl(CV.SUM_CI_BALANCE, 0) as SUM_CI_BALANCE, nvl(CV.SUM_LN_BALANCE, 0) as SUM_LN_BALANCE FROM OCRM_F_CI_CUST_DESC CD left join ( select cust_id, FR_ID, sum(CI_BALANCE) as sum_CI_BALANCE, sum(LN_BALANCE) as sum_LN_BALANCE from OCRM_F_CI_CUST_VIEW where 1 = 1 and FR_ID = '15601' and MGR_ID = '00001' group by cust_id, FR_ID union select cust_id, FR_ID, sum(CI_BALANCE) as sum_CI_BALANCE, sum(LN_BALANCE) as sum_LN_BALANCE from OCRM_F_CI_CUST_VIEW where 1 = 1 and FR_ID = '15601' and MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '00001') group by cust_id, FR_ID) CV on CD.Cust_Id = CV.cust_id and CD.Fr_Id = CV.fr_id WHERE 1 = 1 and CD.FR_ID = '15601') A where 1 = 1 AND ((EXISTS (SELECT 1 FROM OCRM_F_CI_BELONG_CUSTMGR MGR WHERE MGR.CUST_ID = A.CUST_ID AND MGR.MGR_ID = '00001' union (SELECT 1 FROM OCRM_F_CI_BELONG_CUSTMGR MGR WHERE MGR.CUST_ID = A.CUST_ID and MGR.MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '00001'))) )) ORDER BY to_number(SUM_CI_BALANCE) DESC, to_number(SUM_LN_BALANCE) DESC)---返回534199行记录 select count(*) from (SELECT A.* FROM (SELECT CD.*, nvl(CV.SUM_CI_BALANCE, 0) as SUM_CI_BALANCE, nvl(CV.SUM_LN_BALANCE, 0) as SUM_LN_BALANCE FROM OCRM_F_CI_CUST_DESC CD left join (select cust_id, FR_ID, sum(CI_BALANCE) as sum_CI_BALANCE, sum(LN_BALANCE) as sum_LN_BALANCE from OCRM_F_CI_CUST_VIEW where 1 = 1 and FR_ID = '15601' and (MGR_ID = '00001' OR MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '00001')) group by cust_id, FR_ID) CV on CD.Cust_Id = CV.cust_id and CD.Fr_Id = CV.fr_id WHERE 1 = 1 and CD.FR_ID = '15601') A where 1 = 1 AND ((EXISTS (SELECT 1 FROM OCRM_F_CI_BELONG_CUSTMGR MGR WHERE MGR.CUST_ID = A.CUST_ID AND (MGR.MGR_ID = '00001' OR MGR.MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '00001'))))) ORDER BY to_number(SUM_CI_BALANCE) DESC, to_number(SUM_LN_BALANCE) DESC)--534137为什么改成union后相差62条记录呢？改成union 后 因为cust_id和fr_id一样,可是sum(CI_BALANCE) as sum_CI_BALANCE和sum(LN_BALANCE) as sum_LN_BALANCE不一致SQL> select * from ( select cust_id, FR_ID, sum(CI_BALANCE) as sum_CI_BALANCE, sum(LN_BALANCE) as sum_LN_BALANCE from OCRM_F_CI_CUST_VIEW where 1 = 1 and FR_ID = '15601' and MGR_ID = '00001' group by cust_id, FR_ID union select cust_id, FR_ID, sum(CI_BALANCE) as sum_CI_BALANCE, sum(LN_BALANCE) as sum_LN_BALANCE from OCRM_F_CI_CUST_VIEW where 1 = 1 and FR_ID = '15601' and MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '00001') group by cust_id, FR_ID) where cust_id='133030219800426732X' 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 ;CUST_ID FR_ID SUM_CI_BALANCE SUM_LN_BALANCE--------------------- ------------------------------ -------------- --------------133030219800426732X 15601 0 0133030219800426732X 15601 155216.98 0SQL> select * from ( select cust_id, FR_ID, sum(CI_BALANCE) as sum_CI_BALANCE, sum(LN_BALANCE) as sum_LN_BALANCE from OCRM_F_CI_CUST_VIEW where 1 = 1 and FR_ID = '15601' and MGR_ID = '00001' group by cust_id, FR_ID) where cust_id='133030219800426732X' 2 3 4 5 6 7 8 9 10 ;CUST_ID FR_ID SUM_CI_BALANCE SUM_LN_BALANCE--------------------- ------------------------------ -------------- --------------133030219800426732X 15601 0 0可是原SQL中结果为:SQL> select * from (select cust_id, FR_ID, sum(CI_BALANCE) as sum_CI_BALANCE, sum(LN_BALANCE) as sum_LN_BALANCE from OCRM_F_CI_CUST_VIEW where 1 = 1 and FR_ID = '15601' and (MGR_ID = '00001' OR MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '00001')) group by cust_id, FR_ID) where cust_id='133030219800426732X' 2 3 4 5 6 7 8 9 10 11 12 13 14 ;CUST_ID FR_ID SUM_CI_BALANCE SUM_LN_BALANCE--------------------- ------------------------------ -------------- --------------133030219800426732X 15601 155216.98 0多出了SUM_CI_BALANCE =0的记录