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 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的記錄