Oracle中一些不同于其他数据库的常用SQL语句

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语句
https://blog.qingfengju.com/index.asp?id=240

上一篇： 修改Windows路由表实现双网卡同时上网
下一篇： 体验Linux.4 Debian5.0.7下安装CodeBlocks10.05

分类：数据库 查看次数：13773 发布时间：2010/11/10 13:12:15