Oracle 查询特性

排序

空值排序

WITH

语法

1
2
3
4
with w_dual as (
  select * from dual
)
SELECT * FROM w_dual

保留最新数据

1
2
3
4
5
6
7
8
9
10
11
12
delete from T_TZZ_TZZ_MEMBER where id in (
  with w_member_error as (
    select * from T_TZZ_TZZ_MEMBER where stu_id in (
	  select stu_id from T_TZZ_TZZ_MEMBER group by stu_id having count(1) > 1
    )
  )
  select id from w_member_error a where id not in(
    select id from w_member_error a where not exists (
        select 1 from w_member_error b where a.modify_date < b.modify_date and a.stu_id = b.stu_id
    )
  )
);

PARTITION BY

初始化分组编号

1
2
3
4
5
UPDATE omc_om_wtsafety a SET a.safetynum = (
    SELECT row_number() OVER(PARTITION BY wtnum ORDER BY wtsafetynum ASC) seqnum
    FROM omc_om_wtsafety b
    WHERE a.wtsafetyid = b.wtsafetyid
);

获取最新记录

1
2
3
4
5
6
7
8
9
10
11
12
13
update T_GUANGGAO_NR a set UPDATED_AT = (
  select b.OPERATORTIME from (
    select id, NOTICEID, OPERATORTIME, CONTENT,
		row_number() OVER(PARTITION BY NOTICEID ORDER BY OPERATORTIME desc) rn
    from T_ZIXUN_LOG
  ) b where rn = 1 and a.id = b.NOTICEID
), UPDATED_CONTENT = (
  select b.CONTENT from (
    select id, NOTICEID, OPERATORTIME, CONTENT,
		row_number() OVER(PARTITION BY NOTICEID ORDER BY OPERATORTIME desc) rn
    from T_ZIXUN_LOG
  ) b where rn = 1 and a.id = b.NOTICEID
);

Hierarchical Queries

  • START WITH specifies the root row(s) of the hierarchy.
  • CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.
  • ORDER SIBLINGS BY preserve ordering within the hierarchy.
  • CONNECT_BY_ROOT
  • SYS_CONNECT_BY_PATH
1
2
3
4
5
select last_name, employee_id, manager_id, level
from employees
start with employee_id = 100
connect by prior employee_id = manager_id
order siblings by last_name;

参考

CREATE DATABASE LINK

1
2
3
4
5
6
7
8
9
(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.210)(PORT = 1521))
	)
	(CONNECT_DATA =
		(SERVER = DEDICATED)
		(SERVICE_NAME = orcl)
	)
)