1
2
3
4
5
6
title: Oracle-项目实践
tags: Oracle-项目实践
date: 2021-08-19 15:57:10
categories: 
  - 数据库
  - Oracle

集合操作（交集、并集、差集）& 子查询之单行子查询

https://blog.csdn.net/wy_0928/article/details/51158553

根据字符将字段拆分

1
2
3
select GEOCOORDINATES,
REGEXP_SUBSTR(GEOCOORDINATES, '[^,]+', 1, 1) lng,
REGEXP_SUBSTR(GEOCOORDINATES, '[^,]+', 1, 2) lat from SCM_CITYCOMPONENT;

rownum

前N条数据

1
select * from user where rownum <= N;

第N条数据

1
select * from user where rownum = N;

union 与 union all

  1. union all 仅仅是简单的合并查询结果, 并不会做去重操作, 也不会排序, 所以 union all 效率要比 union 高，在确定没有重复记录的情况下, 尽量使用union all
  2. 根据索引查询时，union 比 or 效率更高。or 不会使用索引

union 或者 union all 与 order by 的联合使用

1
2
3
4
5
select * from(select  *  from table order by a)
union
select * from (select * from table1 order by b)
union
select * from (select * from table2 order by c);

NUMBER （ precision， scale）

参考 https://www.cnblogs.com/oumyye/p/4448656.html

1
2
3
4
1. precision 有效位数，包括整数位+小数位
2. scale 小数点后精确位数
3. precision-scale 整数部分最大位数
4. precision 与 scale 相等，则表示整数部分为0

按照某字段排序，为null则放到开始或最后

1
2
3
order by g.tracktime desc nulls first

order by g.tracktime desc nulls last

分组后返回非分组字段组合

1
SELECT class_id, wm_concat(name), COUNT(*) num FROM students GROUP BY class_id;

字符串分隔成多列

1
2
3
4
select 
REGEXP_SUBSTR('123,456,789', '[^,]+', 1, 1) d1,
REGEXP_SUBSTR('123,456,789', '[^,]+', 1, 2) d2,
REGEXP_SUBSTR('123,456,789', '[^,]+', 1, 3) d3 from dual;
D1 D2 D3
123 456 789

oracle 分组后取每组第一条数据

1
2
//根据 x 列分组，y 列倒序，取每组第一条
SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t.x ORDER BY t.y DESC) rn, t.* FROM test t) WHERE rn = 1;

根据分隔字符将单字段拆分成多行

参考：https://www.geek-share.com/detail/2791571608.html

问题：附件表 AFFIXURL 字段缺少分隔符|，导致产生错误数据

解决：根据 /scm 分隔，将单字段拆分成多行，主键加上 rownum 做为新主键值来去重

注意：SQL 中 M为分隔符长度，N = M-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select AFFIXID+ROWNUM as AFFIXID,PROCID, EVENTCODE, FILETYPE, AFFIXTYPE, '/scm'||AFFIXURL AFFIXURL from (SELECT AFFIXID,PROCID, EVENTCODE, FILETYPE, AFFIXTYPE,
       substr(a.AFFIXURL, instr(a.AFFIXURL, '/scm', 1, levels.lvl) + M,
              instr(a.AFFIXURL, '/scm', 1, levels.lvl + 1) - (instr(a.AFFIXURL, '/scm', 1, levels.lvl) + 1) -
              N) as AFFIXURL
FROM (SELECT AFFIXID,PROCID, EVENTCODE, FILETYPE, AFFIXTYPE,
             '/scm' || AFFIXURL || '/scm' AS AFFIXURL,
             (length(AFFIXURL || '/scm') - nvl(length(REPLACE(AFFIXURL, '/scm')), 0)) / M AS cnt
      FROM SCM_AFFIX
      where EVENTCODE like 'C20200710%' and length(AFFIXURL) > 55) a,
     (SELECT rownum AS lvl
      FROM (SELECT (MAX(length(AFFIXURL || '/scm') - nvl(length(REPLACE(AFFIXURL, '/scm')), 0))) / M max_len
            FROM SCM_AFFIX
            where EVENTCODE like 'C20200710%'and length(AFFIXURL) > 55)
      CONNECT BY LEVEL <= max_len) levels
WHERE levels.lvl <= a.cnt
ORDER BY AFFIXID, levels.lvl) a where a.AFFIXURL is not null

查看表中多余的重复记录，重复记录是根据单个字段（pid）来判断

1
select * from SGP_PREGNANCY where pid in (select pid from SGP_PREGNANCY group by pid having count(pid) > 1)

清除查询缓存

1
2
3
ALTER SYSTEM FLUSH SHARED_POOL
ALTER SYSTEM FLUSH BUFFER_CACHE
ALTER SYSTEM FLUSH GLOBAL CONTEXT

分页优化

1
2
3
4
5
6
7
8
9
10
11
12
13
// 普通分页
select * from (select rownum rn, p.* from
(
select g.* from SGP_PERSON g WHERE 1 = 1
) p
where rownum<=239178) where rn >239168

// 优化后分页
select * from SGP_PERSON where rowid in ( select rid from (select rownum rn, rid from
(
SELECT rowid rid, g.* from SGP_PERSON g WHERE 1 = 1
)
where rownum<=239178) where rn >239168)

函数

replace

示例：

1
2
3
4
5
//name 字段值中逗号替换为空
replace(name, ',')

//name 字段值中逗号替换为#
replace(name, ',', '#')

dbms_random.value

获取 0-1 之间随机值

1
2
// 获取 80 到 100之间随机小数,小数点后保留两位
SELECT TRUNC(80+20*dbms_random.value, 2) FROM dual

trunc

日期

1
2
3
4
5
6
7
8
select trunc (sysdate) from dual --2013-01-06 今天的日期为 2013-01-06
select trunc (sysdate, 'mm') from dual --2013-01-01 返回当月第一天.
select trunc (sysdate,'yy') from dual --2013-01-01 返回当年第一天
select trunc (sysdate,'dd') from dual --2013-01-06 返回当前年月日
select trunc (sysdate,'yyyy') from dual --2013-01-01 返回当年第一天
select trunc (sysdate,'d') from dual --2013-01-06 (星期天) 返回当前星期的第一天
select trunc (sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为 17:35
select trunc (sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC () 函数没有秒的精确

数字

TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC () 函数截取时不进行四舍五入

1
2
3
4
5
6
7
8
9
select trunc(123.458) from dual --123
select trunc(123.458,0) from dual --123
select trunc(123.458,1) from dual --123.4
select trunc(123.458,-1) from dual --120
select trunc(123.458,-4) from dual --0
select trunc(123.458,4) from dual --123.458
select trunc(123) from dual --123
select trunc(123,1) from dual --123
select trunc(123,-1) from dual --120

substr

substr(字符串，截取开始位置，截取长度) // 返回截取的字

1
2
3
4
5
6
7
8
9
10
11
substr ('Hello World',0,1) // 返回结果为 'H'  * 从字符串第一个字符开始截取长度为 1 的字符串

substr ('Hello World',1,1) // 返回结果为 'H'  *0 和 1 都是表示截取的开始位置为第一个字符

substr ('Hello World',2,4) // 返回结果为 'ello'

substr ('Hello World',-3,3)// 返回结果为 'rld' * 负数 (-i) 表示截取的开始位置为字符串右端向左数第 i 个字符

测试：

select substr('Hello World',-3,3) value from dual;

instr

类似 indexof

  • UTF8 编码
1
2
3
4
5
select instr('保定市南市区','市',1,1)-1 from dual;
解释：1. '保定市南市区' =>可以是表达式，也可以是具体数据
     2. '市'=>为分离的标志，这里为两组数据中的“市”这个字
     3. 第一个1为从左边开始，如果为-1，则从右边开始。
     4. 第二个1为“市”出现的第几次。

nvl(p1, p2)

p1 为空，则返回 p2，不为空，则返回p1

nvl2(expre1,expre2,expre3)

如果 expre1 不为空，则返回 expre2，如果为空，返回expre3

nullif (expre1,expre2)

比较两个参数，如果参数 1 和参数 2 相等，返回 null
如果不相等，返回第一个参数

coalesce(expre1,expre2,expre3)

参数 1：如果第一参数不为空，返回第一个参数。
参数 2：如果参数 1 为空，则返回第二个参数。
参数 3：如果参数 1 和参赛 2 都为空，返回第三个参数。

case when then

1
2
3
4
5
6
7
8
SELECT ENAME,SAL,JOB, 
CASE JOB 
  WHEN 'MANAGER' THEN SAL+100
  WHEN 'ANALYSIS' THEN SAL+200
  WHEN 'SALESMAN' THEN SAL+300
  ELSE SAL+400
  END "RESULTS"
  FROM EMP;

DECODE

DECODE(col|expression ,search1,result1
[,search2,result2]
[,default])
如果 default 值被省略了，那么当 search 值没有与任何的 math 值匹配，将返回 null 值。

1
2
3
SELECT ENAME,JOB,SAL,
  DECODE(JOB, 'MANAGER', SAL+100, 'SALESMAN',SAL+200, SAL) REVISED_SAL
  FROM EMP;

行转列 pivot 、列转行 unpivot

https://blog.csdn.net/xiaokui_wingfly/article/details/42419207