1.从排序后的记录集中查询前几个结果(select top n .. order by..)
Select * FROM 
(Select * FROM <table_name> orDER BY id)
Where ROWNUM >= n

2.用一个表的数据更新另一个表

MERGE INTO <table1> a
USING <table2> b
ON (a.id=b.id)
WHEN MATCHED THEN
SET a.f1=b.f1,a.f2=b.f2

3.高效删除重复记录(重复的只保留一条)

Delete FROM <table_name> a
Where a.ROWID > (Select MIN(b.ROWID) FROM <table_name> b Where a.id=b.id)

4.将各分组的记录序号列出来

Select row_number() over (PARTITION BY <col1> orDER BY <col2>) as order_num,t.* 
from <table_name> t

应用示例:对于某字段相同的记录,只列出其中的一条

select * from 
(
  select row_number() OVER (PARTITION BY <col1> orDER BY <col1>) as order_num,
  n.* from <table_name> n
)
where order_num=1

5.查找重复超过1条的记录

select a,b,count(*) from testtable
group by a,b
having count(*)>1

本文链接地址: Oracle中一些不同于其他数据库的常用SQL语句
http://qingfengju.com/index.asp?id=240

分类:数据库 查看次数:6267 发布时间:2010-11-10 13:12:15