我的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
执行结果是没有数据的，可数据库中有数据的。
既然是 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
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
1)当not exists()中的子查询select top 20没有数据，not exists()条件成立，但是select top 10也是没有数据的。
2)当not exists()中的子查询select top 20有数据，not exists()条件不成立，这样select top 10的数据在该条件下也是没有数据的。
分页的方法有多种，row_number(),identity(int,1,1)临时表，not in等 都可以。
这里就写not in;参数@page是当前第几页
select top 10*@page left(loginDate,10),skipUrl,count(loginDate) as qty from loginlog where 主键 not in (select top 10*(@page-1) 主键 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