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