表结构是这样
id name
1 asdf
2 asdf
3 asdf
4 asf
a asdf
5 asdf
6 asdf现在实现的结果是查询 3 ~~ 6之间的数据 ,,大家看5上面是个a哦,我现在用 between 出错id 是varchar类型
大家有什么好的办法实现这个查询不,我也不可能一一限制id
id name
1 asdf
2 asdf
3 asdf
4 asf
a asdf
5 asdf
6 asdf现在实现的结果是查询 3 ~~ 6之间的数据 ,,大家看5上面是个a哦,我现在用 between 出错id 是varchar类型
大家有什么好的办法实现这个查询不,我也不可能一一限制id
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] nvarchar(1),[name] nvarchar(4))
Insert #T
select N'1',N'asdf' union all
select N'2',N'asdf' union all
select N'3',N'asdf' union all
select N'4',N'asf' union all
select N'a',N'asdf' union all
select N'5',N'asdf' union all
select N'6',N'asdf'
Go
Select * from #T where ID between '3' and '6'
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-20 10:30:00
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(1),[name] varchar(4))
insert [tb]
select '1','asdf' union all
select '2','asdf' union all
select '3','asdf' union all
select '4','asf' union all
select 'a','asdf' union all
select '5','asdf' union all
select '6','asdf'
--------------开始查询--------------------------
select
*
from
(
select *,id0=row_number()over(order by getdate()) from [tb]
)t
where id0 between 3 and 6
----------------结果----------------------------
/*id name id0
---- ---- --------------------
3 asdf 3
4 asf 4
a asdf 5
5 asdf 6(4 行受影响)
*/
select * from 表 where id between 3 and 6
declare @tb table (id varchar(1),name varchar(4))
insert into @tb
select '1','asdf' union all
select '2','asdf' union all
select '3','asdf' union all
select '4','asf' union all
select 'a','asdf' union all
select '5','asdf' union all
select '6','asdf'select * ,px=identity(int,1,1) into #t from @tbselect id,name from #t where px between
(select px from #t
where id='3')
and
(select px from #t
where id='6'
)id name
---- ----
3 asdf
4 asf
a asdf
5 asdf
6 asdf(5 行受影响)drop table #t
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] nvarchar(1),[name] nvarchar(4))
Insert #T
select N'1',N'asdf' union all
select N'2',N'asdf' union all
select N'3',N'asdf' union all
select N'4',N'asf' union all
select N'a',N'asdf' union all
select N'5',N'asdf' union all
select N'6',N'asdf'
Go
;with cte
as
(
select row_Number() over(order by getdate()) nid,id,name from #T
)
select id,name from cte t
where nid between(select top 1 nid from cte where id='3')
and (select top 1 nid from cte where id='6')
between '3' and '6' 就可以了。
select
*
from
(
select *,id0=row_number()over(order by getdate()) from [tb]
)t
where id0 between 3 and 6