Select count (*) from (select. * 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 = '000000' and MGR_ID = '000000' group by cust_id, FR_ID union select cust_id, FR_ I D, 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 = '000000' and MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '2013') 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 = '2013') A where 1 = 1 AND (EXISTS (SELECT 1 FROM OCRM_F_CI_BELONG_CUSTMGR M Gr where mgr. CUST_ID =. CUST_ID and mgr. MGR_ID = '000000' union (SELECT 1 FROM OCRM_F_CI_BELONG_CUSTMGR mgr where mgr. CUST_ID =. CUST_ID and MGR. MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '000000') order by to_number (SUM_CI_BALANCE) DESC, to_number (SUM_LN_BALANCE) DESC) --- return the select count (*) from (select. * 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 = '000000' and (MGR_ID = '000000' OR MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '000000 ')) 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 = '2013') A where 1 = 1 AND (EXISTS (SELECT 1 FROM OCRM_F_CI_BELONG_CUSTMGR mgr where mgr. CUST_ID =. CUST_ID AND (MGR. MGR_ID = '000000' or mgr. MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '000000') order by to_number (SUM_CI_BALANCE) DESC, to_number (SUM_LN_BALANCE) DESC) -- Why are 62 records different from each other after 534137 is changed to union? After changing to union, because cust_id is the same as fr_id, but sum (CI_BALANCE) as sum_CI_BALANCE and sum (LN_BALANCE) as sum_LN_BALANCE are inconsistent 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 = '2016' and MGR_ID = '2016' group by cust_id, FR_ID select union cust_id, FR_ID, sum (CI_BALANCE) as sum_CI_BALANCE, sum (LN_BALANCE) as sum_LN_BALANCE from balance where 1 = 1 and FR_ID = '2013' and MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '2016 ') group by cust_id, FR_ID) where cust_id = '133030219800316732x '2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20 21 22 23 24; CUST_ID FR_ID limit SUM_LN_BALANCE limit -------------- ------------ 133030219800426732X15601 0 0133030219800426732X15601 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 = '000000' and MGR_ID = '000000' group by cust_id, FR_ID) where cust_id = 'hangzhou' 2 3 4 5 6 7 8 9 10; CUST_ID FR_ID SUM_CI_BALANCE SUM_LN_BALANCE ----------------------- begin ------------ 133030219800426732X15601 0 0 but the original SQL result is: 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 = '000000' and (MGR_ID = '000000' OR MGR_ID IN (SELECT USER_ID FROM ADMIN_AUTH_MANAGE_ACCOUNT WHERE MANAGE_ID = '000000 ')) group by cust_id, FR_ID) where cust_id = '1330219800316732x '2 3 4 5 6 7 8 9 10 11 12 13 14; CUST_ID FR_ID SUM_CI_BALANCE SUM_LN_BALANCE ------------------------------------------------ ------------------ 133030219800426732X15601 155216.98 0 More SUM_CI_BALANCE = 0 records