注册 登录
编程论坛 SQL Server论坛

关于SQl语句分页问题

chinacompass 发布于 2011-05-03 14:55, 524 次点击
数据是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)
分页的效果整合到上面查询语句中?我试了几次,但查询结果都是一样的!希望帮我写一下,任意一条都可以,谢谢…………
4 回复
#2
go20112011-05-03 18:49
select top 2 * from (select * from AlertmessageLog A
where id=(select min(id) from AlertmessageLog where alertmessagelog=A.alertmessagelog )
 ) as b where id not in (select top 3 id from b)
#3
chinacompass2011-05-03 20:21
回复 2楼 go2011
感谢你的回复!但有错,说b对像错误
#4
go20112011-05-03 23:36
很抱歉,改成下面这样应该就可以了
select top 2 * from (select * from AlertmessageLog A
where id=(select min(id) from AlertmessageLog where alertmessagelog=A.alertmessagelog )
) as b where id not in (select top 3 id from (select * from AlertmessageLog A
where id=(select min(id) from AlertmessageLog where alertmessagelog=A.alertmessagelog )
) as b
)
#5
chinacompass2011-05-04 00:01
回复 4楼 go2011
你好!请不要说抱歉,这样我会更不好意思!问题是我问的,你来回答我已经非常感谢了!这次没有错误!谢谢了!
1