----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-25 16:32:16
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([filed] varchar(7))
insert [huang]
select '年龄>20' union all
select '30' union all
select '40' union all
select '50' union all
select 'gg' union all
select 'aa'
--------------开始查询--------------------------select * from [huang] WHERE ISNUMERIC([filed] )>0 AND [filed] >30 AND [filed]<50
----------------结果----------------------------
/*
[filed]
*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-25 16:32:16
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([filed] varchar(7))
insert [huang]
select '年龄>20' union all
select '30' union all
select '40' union all
select '50' union all
select 'gg' union all
select 'aa'
--------------开始查询--------------------------select * from [huang] WHERE ISNUMERIC([filed] )>0 AND [filed] >30 AND [filed]<50
----------------结果----------------------------
/*
[filed]
*/
解决方案 »
- sql2008和sql2000的发布订阅问题,求大神解答
- SQL2000中,求以分钟记录的数据按小时查询,并求快速查询中值的方法
- insert语句出错
- 求一句SQL语句,谢谢了
- 使用LoadRunner对C/S系统进行测试的问题
- 对于很复杂的SQL语句,我应该如何理解?
- forward-only cursro如何翻译?
- 把一个字段的值按照条件转移到另外一个字段,数据量很大,是否要用存储过程来做呢?
- 使用循環與遊標,哪個效率高?
- 如何在SQL Server上每天执行一个创建表的SQL语句?
- 关于SQLSERVER找到重复记录,然后将其中ID最大的记录DEMO置为“重复”
- 有一个几千条的txt 如何调用bulk insert 更新到数据库
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([filed] varchar(7))
insert [tb]
select '年龄>20' union all
select '30' union all
select '40' union all
select '50' union all
select 'gg' union all
select 'aa'
select *
from tb
where ISNUMERIC(filed) = 1 and filed > 30 and filed < 50
/*
filed
40
*/
go
create table [tb]([filed] varchar(7))
insert [tb]
select '年龄>20' union all
select '30' union all
select '40' union all
select '50' union all
select 'gg' union all
select 'aa'
select *
from tb
where filed > '30' and filed < '50'
/*
filed
40
*/
年龄>20
30
40
50
gg
aa
4
用上面的语句,就会把4也检索出来!
这个还是可以的,可以过滤4,只显示40:if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([filed] varchar(7))
insert [tb]
select '年龄>20' union all
select '30' union all
select '40' union all
select '50' union all
select 'gg' union all
select 'aa' union all
select '4'
select *
from tb
where ISNUMERIC(filed) = 1 and filed > 30 and filed < 50
/*
filed
40
*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-25 16:32:16
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([filed] varchar(7))
insert [huang]
select '年龄>20' union all
select '30' union all
select '40' union all
select '50' union all
select 'gg' union all
select 'aa'union all
select '4'
--------------开始查询--------------------------select * from [huang] WHERE ISNUMERIC([filed] )>0 AND [filed] >30 AND [filed]<50
----------------结果----------------------------
/*
filed
-------
40
*/
数据库里的数据类型不是varchar,而是nvarchar,不好意思啊!
这样就行哈:if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([filed] nvarchar(7))
insert [tb]
select '年龄>20' union all
select '30' union all
select '40' union all
select '50' union all
select 'gg' union all
select 'aa' union all
select '4'
select [filed]
from
(
select *,
case when ISNUMERIC(filed) = 1
then [filed]
else null
end field_t
from tb
)t
where ISNUMERIC(field_t) = 1 and field_t > 30 and field_t < 50
/*
filed
40
*/
把数值型的数据抽出来,独立成一个数值型的列,其他的手工处理
select [filed]
from
(
select *,
case when ISNUMERIC(filed) = 1
then [filed]
else null
end field_t
from tb
)t
语句,目前我改变不了视图,所以只能想在where条件里做过滤!
对了 ,这个语句不行吗:
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([filed] nvarchar(7))
insert [tb]
select '年龄>20' union all
select '30' union all
select '40' union all
select '50' union all
select 'gg' union all
select 'aa' union all
select '4'
select [filed]
from
(
select *,
case when ISNUMERIC(filed) = 1
then [filed]
else null
end field_t
from tb
)t
where ISNUMERIC(field_t) = 1 and field_t > 30 and field_t < 50
/*
filed
40
*/
可以这么写:
select col1,cast(col2 as int) as col2 into #tb from tb where isnumeric(col2)=1然后再从这个临时表取数据:select * from #tb where col2.....如果数据量大的话可以考虑给临时表加索引
临时表跟你框架有什么关系?你把数据取出来丢给前台就是了,你直接where过滤不也是要取出数据给前台吗?
(SELECT ROW_NUMBER()
OVER (order by CreateTime DESC) AS rows ,*
FROM VEdsPartPrefabAndPartHis where isnumeric(filed)=1 ) AS main_temp
WHERE rows BETWEEN 1 AND 100其中红色部分是我在后台可以修改的,可以传值到这个分页存储过程里,其他的我无法更改,看看怎么修改,请指教!
(SELECT ROW_NUMBER()
OVER (order by CreateTime DESC) AS rows ,*
FROM VEdsPartPrefabAndPartHis where isnumeric(filed)=1 ) AS main_temp
WHERE rows BETWEEN 1 AND 100