我从实际数据中提取创建了133个测试数据执行了楼主的语句,并没有报错,大约语句没有问题吧: SQL> select SFZH,XB,(TO_NUMBER(DECODE(LENGTH(SFZH),18,SUBSTR(SFZH,17,1),15,SUBSTR(SFZH,15,1)))+TO_NUMBER(XB)) 2 from TMP 3 where 4 DECODE(LENGTH(SFZH),18,SUBSTR(SFZH,17,1),15,SUBSTR(SFZH,15,1)) BETWEEN '0' AND '9' 5 AND 6 XB IN('1','2') 7 AND 8 MOD((TO_NUMBER(DECODE(LENGTH(SFZH),18,SUBSTR(SFZH,17,1),15,SUBSTR(SFZH,15,1)))+TO_NUMBER(XB)),2)=1 9 ;未选定行建议楼主查一下 select DECODE(LENGTH(AAC002),18,SUBSTR(AAC002,17,1),15,SUBSTR(AAC002,15,1)) AS A,COUNT(*) from jhk.jf3 GROUP BY DECODE(LENGTH(AAC002),18,SUBSTR(AAC002,17,1),15,SUBSTR(AAC002,15,1)); SELECT TO_NUMBER(AAC004),COUNT(*) FROM jhk.jf3 GROUP BY TO_NUMBER(AAC004); 也许是数据上的问题?
建议用PL/SQL存储过程, 这样的SQL可读性太差~ 不比存储过程里 IF ELSE看的多清楚啊~~
换了一种写法: select count(*) from ( select * from jhk.jf2 where (DECODE(LENGTH(AAC002),18,SUBSTR(AAC002,17,1),15,SUBSTR(AAC002,15,1)) between '0' and '9' ) ) where (MOD(TO_NUMBER(DECODE(LENGTH(AAC002),18,SUBSTR(AAC002,17,1),15,SUBSTR(AAC002,15,1)))+TO_NUMBER(AAC004),2)=1) ; 这样就行了. where后面的条件判断有先后次序的.谢谢各位好心人.散分拉
SQL> select SFZH,XB,(TO_NUMBER(DECODE(LENGTH(SFZH),18,SUBSTR(SFZH,17,1),15,SUBSTR(SFZH,15,1)))+TO_NUMBER(XB))
2 from TMP
3 where
4 DECODE(LENGTH(SFZH),18,SUBSTR(SFZH,17,1),15,SUBSTR(SFZH,15,1)) BETWEEN '0' AND '9'
5 AND
6 XB IN('1','2')
7 AND
8 MOD((TO_NUMBER(DECODE(LENGTH(SFZH),18,SUBSTR(SFZH,17,1),15,SUBSTR(SFZH,15,1)))+TO_NUMBER(XB)),2)=1
9 ;未选定行建议楼主查一下 select DECODE(LENGTH(AAC002),18,SUBSTR(AAC002,17,1),15,SUBSTR(AAC002,15,1)) AS A,COUNT(*)
from jhk.jf3
GROUP BY DECODE(LENGTH(AAC002),18,SUBSTR(AAC002,17,1),15,SUBSTR(AAC002,15,1));
SELECT TO_NUMBER(AAC004),COUNT(*) FROM jhk.jf3 GROUP BY TO_NUMBER(AAC004);
也许是数据上的问题?
这样的SQL可读性太差~
不比存储过程里 IF ELSE看的多清楚啊~~
select count(*) from
(
select * from jhk.jf2 where
(DECODE(LENGTH(AAC002),18,SUBSTR(AAC002,17,1),15,SUBSTR(AAC002,15,1)) between '0' and '9' )
)
where
(MOD(TO_NUMBER(DECODE(LENGTH(AAC002),18,SUBSTR(AAC002,17,1),15,SUBSTR(AAC002,15,1)))+TO_NUMBER(AAC004),2)=1)
;
这样就行了.
where后面的条件判断有先后次序的.谢谢各位好心人.散分拉