测试了一下,好像楼主的没问题啊,可以通过
------------------------------------------------------
create table tblRecvData(content varchar(2))
insert tblRecvData(content)
select '1' union all
select '2' union all
select '3' union all
select 'CX' union all
select '4' union all
select '5'select * from tblRecvData where isnumeric(content)=1 and cast(content as decimal(20,5)) = cast('5' as decimal(20,5))select * from tblRecvData
drop table tblRecvData
------------------------------------------------------
create table tblRecvData(content varchar(2))
insert tblRecvData(content)
select '1' union all
select '2' union all
select '3' union all
select 'CX' union all
select '4' union all
select '5'select * from tblRecvData where isnumeric(content)=1 and cast(content as decimal(20,5)) = cast('5' as decimal(20,5))select * from tblRecvData
drop table tblRecvData
--你改成這樣,看行不?
select * from (select * from tblRecvData where isnumeric(content)=1) A where cast(content as decimal(20,5)) = cast('5' as decimal(20,5))
这个我试过了,还是不行.最后的那个where中的content好像还是从tblRecvData中获取的,而不是从子查询A中获取的.
还是不行,刚才重装了SQLServer,竟然装不上了。总是提示命令行语法错误。
SELECT *
FROM TblJP_Recv
WHERE GroupCode='GC200689155121'
AND RecvTime BETWEEN '2006-08-09' AND '2006-08-12'
AND ISNUMERIC(SmsContent) = 1
AND cast(SmsContent as decimal(20,5)) = cast('5' as decimal(20,5))我觉得比这条语句只是多了两个条件而已:
select * from tblRecvData where isnumeric(content)=1 and cast(content as decimal(20,5)) = cast('5' as decimal(20,5))
--我機子上也可以直接運行成功,^^
--你改成這樣,看行不?
select * from (select * from tblRecvData where isnumeric(content)=1) A where cast(content as decimal(20,5)) = cast('5' as decimal(20,5))
这种写法,SQL在执行时,会自动转换的.
就像 select ... from a,b where a.id=b.id 与 select .. from a inner join b on a.id=b.id一样.楼主可以打下补丁试试.
暂时可以这样代替.
select * from tblRecvData where 1=(case isnumeric(content)
when 1 then cast(content as decimal(20,5)) - cast('5' as decimal(20,5))+1
else
0
end )
SELECT *
FROM TblJP_Recv
WHERE GroupCode='GC200689155121'
AND RecvTime BETWEEN '2006-08-09' AND '2006-08-12'
AND ISNUMERIC(SmsContent) = 1
AND cast(SmsContent as decimal(20,5)) = cast('5' as decimal(20,5))现在将最后一句cast(SmsContent as decimal(20,5))更改如下:
cast(case when isnumeric(SmsContent)=1 then SmsContent else -999999999 as decimal(20,5))返回-999999999也是无奈之举,因为SmsContent中可能包含任何数值,数值的有效范围是由客户决定的,那么0也可能有效。所以我只能固定一个用户不太可能设置的值。不知道你有没有好的解决方案,现在客户那边暂时运行的是这个版本。
SELECT *
FROM TblJP_Recv
WHERE GroupCode='GC200689155121'
AND RecvTime BETWEEN '2006-08-09' AND '2006-08-12'
AND ISNUMERIC(SmsContent) = 1
) as t
where cast(SmsContent as decimal(20,5)) = cast('5' as decimal(20,5))
SELECT *
FROM TblJP_Recv
WHERE GroupCode='GC200689155121'
AND RecvTime BETWEEN '2006-08-09' AND '2006-08-12'
AND ISNUMERIC(SmsContent) = 1
) as t
where cast(SmsContent as decimal(20,5)) = 5
-- 或者 where cast(SmsContent as decimal(20,5)) = cast(5 as decimal(20,5))
--也觉得爽点
用-999999999可能会有问题,可以这样写
cast(case when isnumeric(SmsContent)=1 then SmsContent else null end as decimal(20,5))= cast('5' as decimal(20,5))用null安全
谢谢,我试试去。