mysql和oracle 的异同点

分页上，mysql limit

oracle 通过伪列，

用ROWNUM关键字和用ROWID关键字，下面来详细介绍一下：

1、ROWNUM
其代码为：

SELECT *
  FROM (SELECT ROW_.*, ROWNUM ROWNUM_
          FROM (SELECT *
                  FROM TABLE1
                 WHERE TABLE1_ID = XX
                 ORDER BY GMT_CREATE DESC) ROW_
         WHERE ROWNUM <= 20)
 WHERE ROWNUM_ >= 10;
1
2
3
4
5
6
7
8
这应该是我们大部分程序里所用到的版本，因为这个版本很容易实现复用，中间ROW_部分，就是我们平常写到的sql语句，然后再将起始条数和终止条数作为专门的分页sql语句传入即可查询出我们想要的结果。

    从效率上看，上面的SQL语句在大多数情况拥有较高的效率，主要体现在WHERE ROWNUM <= 20这句上，这样就控制了查询过程中的最大记录数，而在查询的最外层控制最小值。但最大值意味着如果查到了很大的范围（如百万级别的数据），查询就会从很大范围内往里减少，效率就会很低，因此，当面对大数据量时或者优化查询效率时，如果你用了ROWNUM，可以换第二种方法。
1
由以上的方法，又可以引申出3种方式：

A、结合BETWEEN AND 
代码如下：

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT *
                  FROM TABLE1
                 WHERE TABLE1_ID = XX
                 ORDER BY GMT_CREATE DESC) A)
 WHERE RN BETWEEN 10 AND 20;
1
2
3
4
5
6
7
这个就是换汤不换药了，而且查询效率更低，因为：

    Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率，但不能跨越多层。
1
由于查询条件BETWEEN 10 AND 20是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，这个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比原始的查询低得多。

B、结合MINUS

SELECT *
  FROM TABLE1
 WHERE ROWNUM <= 20
MINUS
SELECT * FROM TABLE1 WHERE ROWNUM <= 10;
1
2
3
4
5
查询了两次，效率上更差了一些。

C、ROW_NUMBER() OVER( ORDER BY ORDER_DATE DESC) 
这个和ROWNUM关键字类似，生成的顺序和rownum的语句一样，效率也一样（对于同样有ORDER BY 的ROWNUM语句来说），所以在这种情况下两种用法是一样的。 
而对于分组后查询做分页的话，则是ROWNUM无法实现的，这时只有ROW_NUMBER可以实现，ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段)就能实现分组后编号，其代码为：

SELECT *
  FROM (SELECT a.*,
               ROW_NUMBER() OVER(PARTITION BY TRUNC(order_date) ORDER BY order_date DESC) rn
          FROM TABLE1 a)
 WHERE rn <= 10;
1
2
3
4
5
2、ROWID
ROWID仍旧需求ROWNUM，但方式不同，因此我将其归为另一大类，其代码为：

SELECT *
  FROM (SELECT RID
          FROM (SELECT R.RID, ROWNUM LINENUM
                  FROM (SELECT ROWID RID
                          FROM TABLE1
                         WHERE TABLE1_ID = XX
                         ORDER BY order_date DESC) R
                 WHERE ROWNUM <= 20)
         WHERE LINENUM >= 10) T1,
       TABLE1 T2
 WHERE T1.RID = T2.ROWID;
1
2
3
4
5
6
7
8
9
10
11
从语句上看，共有4层Select嵌套查询，最内层为可替换的不分页原始SQL语句，但是他查询的字段只有ROWID，而没有任何待查询的实际表字段，具体查询实际字段值是在最外层实现的； 
这种方式的原理大致为：

    首先通过ROWNUM查询到分页之后的10条实际返回记录的ROWID，最后通过ROWID将最终返回字段值查询出来并返回；
1
和前面ROWNUM实现方式相比，该SQL的实现方式更加繁琐，通用性也不是非常好，因为要将原始的查询语句分成两部分（查询字段在最外层，表及其查询条件在最内层），想要复用就很困难了； 
但这种实现在特定场景下还是有优势的：比如我们经常要翻页到很后面，比如10000条记录中我们经常需要查9000-9100及其以后的数据；此时该方案效率可能要比前面的高； 
因为前面的方案中是通过ROWNUM <= 9100来控制的，这样就需要查询出9100条数据，然后取最后9000-9100之间的数据，而这个方案直接通过ROWID取需要的那100条数据； 
从不断向后翻页这个角度来看，第一种实现方案的成本会越来越高，基本上是线性增长，而第三种方案的成本则不会像前者那样快速，他的增长只体现在通过查询条件读取ROWID的部分；

因此，在我们实际项目中，基本分页都是可以单靠ROWNUM就可以实现，而在数据量只有几十万的情况下，效率也是够的，如果一定要优化，则可以考虑ROWID。
 

Oracle 查询并删除重复记录的SQL语句

查询及删除重复记录的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 peopleId in (select   peopleId from people group by   peopleId   having count(peopleId) > 1)
and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId )>1)
注:rowid为oracle自带不用该.....
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)
(二)
比方说
在A表中存在一个字段“name”，
而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
Select Name,Count(*) from A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下:
Select Name,sex,Count(*) from A Group By Name,sex Having Count(*) > 1
(三)
方法一
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch ......