根据name的排列取n-m之间
create_name
----------------
a
bf
bfsbfdbdf
cvwer
d
fedv
Gift Products Test Now Want three Days
hyt
mjy
wef
wf
-------------这是name 所有的数据
第一次我取1-5行sql是没有问题的
select Gift_PID,Products_Name_En,
convert(varchar,cast(Set_Points as money),1)as Set_Points,
if_FreeShipping,Products_List_Images,GiftsProducts_Hot,
If_Stock from (select * , new_id = (select count(*) from Gift_productsInfo where t.Products_Name_En > Products_Name_En )+1 from Gift_productsInfo t )m
where new_id between '1' and '5' and
CataLog_ID='101' and Switch='on' order by Products_Name_En ----------------
a
bf
bfsbfdbdf
cvwer
d---------------d?????????但第二次接着取下面的5行就有问题了6-10???
select Gift_PID,Products_Name_En,
convert(varchar,cast(Set_Points as money),1)as Set_Points,
if_FreeShipping,Products_List_Images,GiftsProducts_Hot,
If_Stock from (select * , new_id = (select count(*) from Gift_productsInfo where t.Products_Name_En > Products_Name_En )+1 from Gift_productsInfo t )m
where new_id between '6' and '10' and
CataLog_ID='101' and Switch='on' order by Products_Name_En
--------------------------
fedv
Gift Products Test Now Want three Days
hyt
---------??不知道怎么就取出3行了
create_name
----------------
a
bf
bfsbfdbdf
cvwer
d
fedv
Gift Products Test Now Want three Days
hyt
mjy
wef
wf
-------------这是name 所有的数据
第一次我取1-5行sql是没有问题的
select Gift_PID,Products_Name_En,
convert(varchar,cast(Set_Points as money),1)as Set_Points,
if_FreeShipping,Products_List_Images,GiftsProducts_Hot,
If_Stock from (select * , new_id = (select count(*) from Gift_productsInfo where t.Products_Name_En > Products_Name_En )+1 from Gift_productsInfo t )m
where new_id between '1' and '5' and
CataLog_ID='101' and Switch='on' order by Products_Name_En ----------------
a
bf
bfsbfdbdf
cvwer
d---------------d?????????但第二次接着取下面的5行就有问题了6-10???
select Gift_PID,Products_Name_En,
convert(varchar,cast(Set_Points as money),1)as Set_Points,
if_FreeShipping,Products_List_Images,GiftsProducts_Hot,
If_Stock from (select * , new_id = (select count(*) from Gift_productsInfo where t.Products_Name_En > Products_Name_En )+1 from Gift_productsInfo t )m
where new_id between '6' and '10' and
CataLog_ID='101' and Switch='on' order by Products_Name_En
--------------------------
fedv
Gift Products Test Now Want three Days
hyt
---------??不知道怎么就取出3行了
解决方案 »
- 请问一个关于日期查询条件的问题
- 请问如何实现数据更新
- 在sqlserver原有的表中添加唯一约束的问题
- 请教正则表达式的问题!
- 一个表里面的内容,copy到另一个数据库里的一表里?
- 求教sql语句,急,必定给分
- 又是数据控件绑定的问题!只差最后一步了!
- 关于union取并集,解决问题立即给分,多谢
- 我想在单位上的局域网中,用开发版Sql server2000,一边开发,一边让人使用,不知是否可以?(单位有10台电脑连接我的电脑的数据库)
- 这个SQL的问题怎么解决好???应该简单。
- sql2005导入数据库提示varchar 字转换 bit 发生错误
- sql2005导入数据库提示varchar 字转换 bit 发生错误
from
(
select top m * from tb order by name asc
) order by name desc
(select row=row_number() over(order by getdate()),* from tb) K
where row between 1 and 5
'a' union all select
'bf' union all select
'bfsbfdbdf' union all select
'cvwer' union all select
'd' union all select
'fedv' union all select
'Gift Products Test Now Want three Days' union all select
'hyt' union all select
'mjy'union all select
'wef' union all select
'wf'--4到6
declare @n int
declare @m int
declare @mn int
select @n=4,@m=6
select @mn = @m-@n +1exec('select top '+@mn+' *
from
(
select top '+@m+'* from tb order by create_name asc
) t order by create_name desc ')
create_name
----------------------------------------------------------------------------------------------------
fedv
d
cvwer
select top m * from tablename where id not in (select top n id from tablename order by id asc/*|desc*/) 2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入到临时表
set rowcount n --只取n条结果
select * from 表变量 order by columnname desc 3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列,那么:
先生成一个序列,存储在一临时表中.
select identity(int) id0,* into #temp from tablename 取n到m条的语句为:
select * from #temp where id0 > =n and id0 <= m 如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行:
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
5.如果表里有identity属性,那么简单:
select * from tablename where identity_col between n and m 6.SQL2005开始.可以使用row_number() over()生成行号
;with cte as
(
select id0=row_number() over(order by id),* from tablename
)
select * from cte where id0 between n to m