数据库操作之分页、去重复

1、分页 

Oracle、DB2、SQLSERVER、Mysql、Access分页SQL语句梳理
最近把平时在项目中常用到的数据库分页sql总结了下。大家可以贴出分页更高效的sql语句。
sqlserver分页
 第一种分页方法
 需用到的参数:
 pageSize 每页显示多少条数据
 pageNumber 页数 从客户端传来
 totalRecouds 表中的总记录数 select count (*) from 表名
 totalPages 总页数
 totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
 pages 计算前pages 条数据
 pages= pageSize*(pageNumber-1)
 SQL语句:
 select top pageSize * from 表名 where id  not in (select top pages id from 表名 order by id) order by id
 第二种分页方法
 pageSize 每页显示多少条数据
 pageNumber 页数 从客户端传来
 pages=pageSize*(pageNumber-1)+1
 select top pageSize * from 表名 where id>=(select max(id) from (select top pages id from 表名 order by id asc ) t )

mysql分页
 需用到的参数:
 pageSize 每页显示多少条数据
 pageNumber 页数 从客户端传来
 totalRecouds 表中的总记录数 select count (*) from 表名
 totalPages 总页数
 totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
 pages 起始位置
 pages= pageSize*(pageNumber-1)
 SQL语句:
 select * from 表名 limit pages, pageSize;
 mysql 分页依赖于关键字 limit 它需两个参数:起始位置和pageSize
 起始位置=页大小*(页数-1)
 起始位置=pageSize*(pageNumber -1)

oracle分页
 pageSize 每页显示多少条数据
 pageNumber 页数 从客户端传来
 totalRecouds 表中的总记录数 select count (*) from 表名
 totalPages 总页数
 totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
 startPage 起始位置
 startPage= pageSize*(pageNumber-1)+1
 endPage=startPage+pageSize
 SQL语句
 select a.* from
 (
   select rownum num ,t.* from  表名 t where 某列=某值 order by id asc
 )a
 where a.num>=startPage and a.num<endPage

db2分页
 int startPage=1  //起始页
 int endPage;     //终止页
 int pageSize=5;  //页大小
 int pageNumber=1 //请求页

 startPage=(pageNumber-1)*pageSize+1
 endPage=(startPage+pageSize);

 SQL语句
 select * from (select 字段1,字段2,字段3,字段4,字段5,rownumber() over(order by 排序字段 asc ) as rowid  from 表名 )as a where a.rowid >= startPage AND a.rowid <endPage

access分页
 pageSize 每页显示多少条数据
 pageNumber 页数 从客户端传来
 pages=pageSize*(pageNumber-1)+1
 SQL语句
 select top pageSize * from 表名 where id>=(select max(id) from (select top pages id from 表名 order by id asc ) t )

 

 

2、去重复

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people
where   peopleName in (select peopleName    from people group by peopleName      having count(peopleName) > 1)
and   peopleId not in (select min(peopleId) from people group by peopleName     having count(peopleName)>1)

3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)   

 

6.消除一个字段的左边的第一位：

update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'

 

7.消除一个字段的右边的第一位：

update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'

 

8.假删除表中多余的重复记录（多个字段），不包含rowid最小的记录
update vitae set ispass=-1
where peopleId in (select peopleId from vitae group by peopleId,seq having count(*) > 1) and seq in （select seq from vitae group by peopleId,seq having count(*) > 1）
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

http://hi.baidu.com/stevenjohn/blog/item/f737c7ea84a93dceb21cb1e4.html

http://hi.baidu.com/fxlijun/blog/item/7236cc118699540cb9127ba2.html