select top 15 * from ( select * from table where sid=1004 ) as M where id > (select max (id) from (select top (15 * (20 - 1)) id from ( select * from table where sid=1004 ) as M where order by id --这个位置where order 会报错的 ) as T ) order by id
不好意思, 弄错了一点, 修改如下:select top 15 * from (select * from table where sid=1004) as M where id > (select max (id) from (select top (15 * (20 - 1)) id from (select * from table where sid=1004) as N order by id ) as T ) order by id
为什么不分开来写呢?select * into #a from table where sid=1004 select top (15 * (20 - 1)) id itno #b from #a where order by id select top 15 * from #a where id > (select max (id) from #b ) order by id
declare @pagesize int set @pagesize=15 --每页15条 declare @pageindex int set @pageindex=20 --第20页;with maco as ( select row_number() over (order by id) as num,* from [table] where sid=1004 )select * from maco where num between (@pageindex*(@pagesize-1)+1) and @pageindex*@pagesize
--修正一下,刚才变量写返了declare @pagesize int set @pagesize=15 --每页15条 declare @pageindex int set @pageindex=2 --第20页;with maco as ( select row_number() over (order by id) as num,* from [table] where sid=1004 )select * from maco where num between (@pagesize*(@pageindex-1)+1) and @pageindex*@pagesize
不知道单句SQL、临时表和CTE的效率如何 望LZ实测后贴出来观摩一下
两位好强大啊, 很久没弄SQL编程了, 忘记了select * into #a from table where sid=1004 就可以直接得到临时表, 本来就是想要这个方法的, 现在还得到了5楼的方法, 收获呀
select top 15 * from ( select * from table where sid=1004 ) as M
where id > (select max (id)
from (select top (15 * (20 - 1)) id
from ( select * from table where sid=1004 ) as M
where order by id --这个位置where order 会报错的
) as T
) order by id
弄错了一点,
修改如下:select top 15 * from (select * from table where sid=1004) as M
where id > (select max (id)
from (select top (15 * (20 - 1)) id
from (select * from table where sid=1004) as N
order by id
) as T
) order by id
select top (15 * (20 - 1)) id itno #b from #a where order by id
select top 15 * from #a where id > (select max (id) from #b ) order by id
declare @pagesize int
set @pagesize=15 --每页15条
declare @pageindex int
set @pageindex=20 --第20页;with maco as
(
select row_number() over (order by id) as num,* from [table] where sid=1004
)select * from maco
where num between (@pageindex*(@pagesize-1)+1) and @pageindex*@pagesize
set @pagesize=15 --每页15条
declare @pageindex int
set @pageindex=2 --第20页;with maco as
(
select row_number() over (order by id) as num,* from [table] where sid=1004
)select * from maco
where num between (@pagesize*(@pageindex-1)+1) and @pageindex*@pagesize
望LZ实测后贴出来观摩一下
很久没弄SQL编程了,
忘记了select * into #a from table where sid=1004
就可以直接得到临时表,
本来就是想要这个方法的,
现在还得到了5楼的方法,
收获呀