sql 如下
sum(t1),sum(t2),sum(t3),users from ( select case when state='0' then 1 end as t1,case when state!='0' then 1 end as t2 ,case when state='0' then 0 else to_number(price) end as t3 ,(select attributionc_id from agency where accountno=substr(insurancebaseinfo.auditsale,0,8) ) users from insurancebaseinfo WHERE 1=1 and auditsaledate<='2013-05-27 24:00:00' and auditsaledate>='2012-04-29 00:00:00' ) A where
users in (select accountno from adminusers where isassociation='3'and description='57HYX') group by users我本地结果是
sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
775 15248 304960 ZJDLN服务器上的结果是
sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
14780 177853 355760
775 15248 304960 ZJDLN然后我改掉sql
sum(t1),sum(t2),sum(t3),users from ( select case when state='0' then 1 end as t1,case when state!='0' then 1 end as t2 ,case when state='0' then 0 else to_number(price) end as t3 ,(select attributionc_id from agency where accountno=substr(insurancebaseinfo.auditsale,0,8) ) users from insurancebaseinfo WHERE 1=1 and auditsaledate<='2013-05-27 24:00:00' and auditsaledate>='2012-04-29 00:00:00' ) A where user is not null and users in (select accountno from adminusers where isassociation='3'and description='57HYX') group by users我本地结果是
sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
775 15248 304960 ZJDLN服务器上的结果是
sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
14780 177853 355760
775 15248 304960 ZJDLN
结果依旧没变Oracleselect
sum(t1),sum(t2),sum(t3),users from ( select case when state='0' then 1 end as t1,case when state!='0' then 1 end as t2 ,case when state='0' then 0 else to_number(price) end as t3 ,(select attributionc_id from agency where accountno=substr(insurancebaseinfo.auditsale,0,8) ) users from insurancebaseinfo WHERE 1=1 and auditsaledate<='2013-05-27 24:00:00' and auditsaledate>='2012-04-29 00:00:00' ) A where
users in (select accountno from adminusers where isassociation='3'and description='57HYX') group by users我本地结果是
sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
775 15248 304960 ZJDLN服务器上的结果是
sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
14780 177853 355760
775 15248 304960 ZJDLN然后我改掉sql
sum(t1),sum(t2),sum(t3),users from ( select case when state='0' then 1 end as t1,case when state!='0' then 1 end as t2 ,case when state='0' then 0 else to_number(price) end as t3 ,(select attributionc_id from agency where accountno=substr(insurancebaseinfo.auditsale,0,8) ) users from insurancebaseinfo WHERE 1=1 and auditsaledate<='2013-05-27 24:00:00' and auditsaledate>='2012-04-29 00:00:00' ) A where user is not null and users in (select accountno from adminusers where isassociation='3'and description='57HYX') group by users我本地结果是
sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
775 15248 304960 ZJDLN服务器上的结果是
sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
14780 177853 355760
775 15248 304960 ZJDLN
结果依旧没变Oracleselect
sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
14780 177853 355760
SELECT CASE
WHEN State = '0' THEN
1
END AS T1,
CASE
WHEN State != '0' THEN
1
END AS T2,
CASE
WHEN State = '0' THEN
0
ELSE
To_Number(Price)
END AS T3,
(SELECT Attributionc_Id
FROM Agency
WHERE Accountno = Substr(Insurancebaseinfo.Auditsale,
0,
8)) Users
FROM Insurancebaseinfo
WHERE 1 = 1
AND Auditsaledate <= '2013-05-27 24:00:00'
AND Auditsaledate >= '2012-04-29 00:00:00') a
WHERE USER IS NOT NULL
AND Users IN (SELECT Accountno
FROM Adminusers
WHERE Isassociation = '3'
AND Description = '57HYX')
GROUP BY Users;
根本没有什么特殊字符,两边数据都是一样的,你这和我的没有任何不同
我把null使用此函数nvl,把null的转换成0 查出来的数据是sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
14780 177853 355760 0
775 15248 304960 ZJDLNwhere users!=‘0’
结果仍然是sum(t1) sum(t2) sum(t3) users
----------------------------------------------------
14780 177853 355760 0
775 15248 304960 ZJDLN