Question

Sql 用top not in分页问题

数据是SQl server 2008
有一个表为AlertmessageLog
字段
id 自动编号
DeviceId nvarchar
alertmessagelog nvarchar
comid nvarchar
changetime datatime
这里面存放一百多条数据（有重复）！
id 自动编号里面无重复数据
DeviceId 和comid 有重复数据，但不依靠这两个字段去重复数据！
Alertmessagelog 里面有重复值，去除这个字段的重复数据并显示结果，返回查询结果中要含这个表中全部字段
用以下两种语句均可实现！
1 。select * from AlertmessageLog a
where not exists(select 1 from AlertmessageLog where alertmessagelog=a.alertmessagelog and id<a.id)

2. select * from AlertmessageLog A
where id=(select min(id) from AlertmessageLog where alertmessagelog=A.alertmessagelog )
我想用top 和not in 实现分页
如何把select top 2 * from alertMessageLog where id not in(select top 3 id from alertMessageLog)
分页的效果整合到上面查询语句中？我试了几次，但查询结果都是一样的！希望帮我写一下，任意一条都可以，谢谢…………

Answer 1

这个是你想要的吗？
SELECT top 2 * FROM (select * from AlertmessageLogas A where id=(select max(id) from AlertmessageLog where AlertmessageLog=A.AlertmessageLog)) AS A WHERE id NOT IN ( SELECT top (2*2) id FROM (select * from AlertmessageLogas A where id=(select max(id) from AlertmessageLog where AlertmessageLog=A.AlertmessageLog)) as A)

Answer 2

select top 2 * from AlertmessageLog A
where id=(select min(id) from AlertmessageLog where alertmessagelog=A.alertmessagelog )

这样不就行了吗？

Answer 3

select top 2 * from alertMessageLog where (id not in(select top 3 id from alertMessageLog) And id=(select min(id) from AlertmessageLog as A where alertmessagelog=A.alertmessagelog ))