sql server 2008分组后如何实现分页查询

Question

我的SQL语句是这样的
select left(loginDate,10) ,skipUrl,count(loginDate) FROM loginlog group by skipUrl,left(loginDate,10) order by left(loginDate,10) desc
我自己试了很多总方式都不对的，不知道问题出在哪里的了？希望大侠帮忙解决下的了谢谢！
select top 10 left(loginDate,10) ,skipUrl,count(loginDate)from loginlog
where not exists(select top 20 left(loginDate,10) ,skipUrl,count(loginDate) FROM loginlog group by skipUrl,left(loginDate,10) order by left(loginDate,10) desc )
group by skipUrl,left(loginDate,10) order by left(loginDate,10) desc
执行结果是没有数据的，可数据库中有数据的。

Answer 1

既然是 2008 用公用表达式配合 ROW_NUMBER()OVER()比较好
;with cte as
(
select left(loginDate,10) as loginDate ,skipUrl,count(loginDate) as loginDatenum FROM loginlog group by skipUrl,left(loginDate,10)
)
,cte1 as
(
select id=row_number()over(partition by loginDate,skipUrl order by loginDate desc),* from cte
)

select loginDate,skipUrl ,loginDatenum from cte1 where id between 10 and 20