oracle常用sql

复制

1、复制表结构以及数据
create table d_table_name as select from s_table_name; 注意并不会创建索引
2、只复制表结构
create table d_table_name as select from s_table_name where 1=2;
3、只复制数据
（1）两个表结构一样
insert into d_table_name select * from s_table_name;
（2）两个表的结构不一样，只复制部分列
insert into d_table_name (column1,column2,column3) select column1x,column2x,column3x from s_table_name;

分组、排序取第N条

（1）分组排序后取第一条，返回一个集合

1
2
3
4
5
6
7
8
9
select t.bh,t.id,t.rn
from (select r.bh,
             r.id,
             row_number() over(partition by r.mainid, r.bh order by r.bh desc) rn
      from a r
      where r.mainid = '9550c8bf-9ab0-4ed6-8c2e-932d7598b0d8'
           and r.bh < 7
     ) t
where t.rn = 1

（2）排序后取第一条，返回一个对象

1
2
3
4
5
6
7
8
9
select t.bh,t.id,t.rn
from (select r.bh,
             r.id,
             row_number() over(order by r.bh desc) rn
      from a r
      where r.mainid = '9550c8bf-9ab0-4ed6-8c2e-932d7598b0d8'
            and r.bh < 7
     ) t
where t.rn = 1

（3）去重排序后取第一条，返回一个对象

1
2
3
4
5
6
7
8
9
SELECT bh
FROM (SELECT A.bh, row_number() over(order by A.bh desc) RN
      FROM (SELECT distinct bh
            FROM a
         	 where MAINID = '9550c8bf-9ab0-4ed6-8c2e-932d7598b0d8'
           	and bh < 7
           ) A
     )
where RN = 1