oracle同样的数据执行的结果不同

[zhouyusunquan]

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


结果依旧没变

[zhouyusunquan]

引用 3 楼 vanjayhsu 的回复:

确定本地和服务器都是连接同一数据库么？

这不是废话，我是说数据一样，数据库当然不是同一个

[zhou_687]

服务器的数据是否含有不可见的特殊字符，建议执行以下SQL看看，把length(Users)长度也查出来，比较：SUM(T1), SUM(T2), SUM(T3), Users,length(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;

[vanjayhsu]

楼主的意思是服务器上多了下面这一行？ sum(t1) sum(t2) sum(t3) users ---------------------------------------------------- 14780 177853 355760

[zhouyusunquan]

引用 1 楼 vanjayhsu 的回复:

楼主的意思是服务器上多了下面这一行？ sum(t1) sum(t2) sum(t3) users ---------------------------------------------------- 14780 177853 355760

是的

[vanjayhsu]

确定本地和服务器都是连接同一数据库么？

[zhouyusunquan]

引用 5 楼 zhou_687 的回复:

服务器的数据是否含有不可见的特殊字符，建议执行以下SQL看看，把length(Users)长度也查出来，比较：SUM(T1), SUM(T2), SUM(T3), Users,length(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;

根本没有什么特殊字符，两边数据都是一样的，你这和我的没有任何不同

[zhouyusunquan]

引用 6 楼 zhouyusunquan 的回复:

[quote=引用 5 楼 zhou_687 的回复:] 服务器的数据是否含有不可见的特殊字符，建议执行以下SQL看看，把length(Users)长度也查出来，比较：SUM(T1), SUM(T2), SUM(T3), Users,length(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;

根本没有什么特殊字符，两边数据都是一样的，你这和我的没有任何不同[/quote] 我把null使用此函数nvl，把null的转换成0 查出来的数据是 sum(t1) sum(t2) sum(t3) users ---------------------------------------------------- 14780 177853 355760 0 775 15248 304960 ZJDLN where users！=‘0’ 结果仍然是 sum(t1) sum(t2) sum(t3) users ---------------------------------------------------- 14780 177853 355760 0 775 15248 304960 ZJDLN