sql改写or 改成union不等价数据变多

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		 0
133030219800426732X   15601				  155216.98		 0

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)
                            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的记录