//正确的显示,下面SQL
select * from Gift_productsInfo where CataLog_ID='101' order by Create_Time desc------------------------------------------------------------
18 101 101.00 true
17 102 102.00 true
16 100 100.00 true
15 99 99.00 true
14 88 8,800.00 true
13 77 7,700.00 true
12 66 6,600.00 true
11 55 5,500.00 true
10 44 4,400.00 true //我现在取上面的数据1-4条,按 Time 排列 --SQlselect 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 Create_Time < t.Create_Time )+1 from Gift_productsInfo t
) m where new_id between 1 and 4 and CataLog_ID='101' and Switch='on' order by Create_Time desc////////确取出13 77 7,700.00 true
12 66 6,600.00 true
11 55 5,500.00 true
10 44 4,400.00 true ////但实际应该输出
18 101 101.00 true
17 102 102.00 true
16 100 100.00 true
15 99 99.00 true -------??不知道怎么改,请大家帮助..谢谢
select * from Gift_productsInfo where CataLog_ID='101' order by Create_Time desc------------------------------------------------------------
18 101 101.00 true
17 102 102.00 true
16 100 100.00 true
15 99 99.00 true
14 88 8,800.00 true
13 77 7,700.00 true
12 66 6,600.00 true
11 55 5,500.00 true
10 44 4,400.00 true //我现在取上面的数据1-4条,按 Time 排列 --SQlselect 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 Create_Time < t.Create_Time )+1 from Gift_productsInfo t
) m where new_id between 1 and 4 and CataLog_ID='101' and Switch='on' order by Create_Time desc////////确取出13 77 7,700.00 true
12 66 6,600.00 true
11 55 5,500.00 true
10 44 4,400.00 true ////但实际应该输出
18 101 101.00 true
17 102 102.00 true
16 100 100.00 true
15 99 99.00 true -------??不知道怎么改,请大家帮助..谢谢
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
if_FreeShipping,Products_List_Images,GiftsProducts_Hot,If_Stock from
(
select * , new_id = (select count(*) from Gift_productsInfo where Create_Time < t.Create_Time )+1 from Gift_productsInfo t
) m where new_id between 1 and 4 and CataLog_ID='101' and Switch='on' order by Create_Time desc
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 Create_Time > t.Create_Time )+1
from
Gift_productsInfo t
) m
where
new_id between 1 and 4
and
CataLog_ID='101'
and
Switch='on'
order by
Create_Time desc