查询的是序列号 比较长 不能用长整型表示用文本表示,最长20个数字
要求查询2个字符串中间的值如
SN
2009080500000001
2009080500000003
2009080500000005
2009080500000014
2009080500000025
2009080500000036
2009080500000047查询>=2009080500000004 <=2009080500000025之间的值结果为
2009080500000005
2009080500000014
2009080500000025
要求查询2个字符串中间的值如
SN
2009080500000001
2009080500000003
2009080500000005
2009080500000014
2009080500000025
2009080500000036
2009080500000047查询>=2009080500000004 <=2009080500000025之间的值结果为
2009080500000005
2009080500000014
2009080500000025
where SN>='2009080500000004' AND SN<='2009080500000025'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([SN] text)
insert [tb]
select '2009080500000001' union all
select '2009080500000003' union all
select '2009080500000005' union all
select '2009080500000014' union all
select '2009080500000025' union all
select '2009080500000036' union all
select '2009080500000047'
---查询---
select
*
from
[tb]
where
cast(SN AS VARCHAR(20)) between '2009080500000004' and '2009080500000025'---结果---
SN
----------------------------------
2009080500000005
2009080500000014
2009080500000025(所影响的行数为 3 行)
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( a varchar(20))
go
insert tb SELECT
'2009080500000001' UNION ALL SELECT
'2009080500000003' UNION ALL SELECT
'2009080500000005' UNION ALL SELECT
'2009080500000014' UNION ALL SELECT
'2009080500000025' UNION ALL SELECT
'2009080500000036' UNION ALL SELECT
'2009080500000047'
go
select *
from tb
where a between 2009080500000004 and 2009080500000025 go
/*
a
--------------------
2009080500000005
2009080500000014
2009080500000025*/
如,
right(sn,8) 得到的部分就可以用int来表示了。
即使int不行,还有bigint,呵呵。
insert @a select
'2009080500000001' union all select
'2009080500000003' union all select
'2009080500000005' union all select
'2009080500000014' union all select
'2009080500000025' union all select
'2009080500000036' union all select
'2009080500000047'select * from @a where cast(sn as varchar(20)) between '2009080500000004' and '2009080500000025' sn
----------------
2009080500000005
2009080500000014
2009080500000025
select
*
from
tb
where
right('00000000000000000000'+SN,20)>=right('0000'+'2009080500000004',20)
AND
right('00000000000000000000'+SN,20)<=right('0000'+'2009080500000025',20)
用于水晶报表的查询between 值1 and 值2
go
create table [tb]([SN] text)
insert [tb]
select '2009080500000001' union all
select '2009080500000003' union all
select '2009080500000005' union all
select '2009080500000014' union all
select '2009080500000025' union all
select '2009080500000036' union all
select '2009080500000047'
select * from tb where CAST(sn as nvarchar(30)) between '2009080500000005' and '2009080500000025'if object_id('[tb]') is not null drop table [tb] --长整型
go
create table [tb]([SN] bigint)
insert [tb]
select '2009080500000001' union all
select '2009080500000003' union all
select '2009080500000005' union all
select '2009080500000014' union all
select '2009080500000025' union all
select '2009080500000036' union all
select '2009080500000047'
select * from tb where sn between 2009080500000005 and 2009080500000025