~~~ 准备员工表与部门表测试数据

row_number() over 唯一分组排序

函数格式：row_number() over([partition by column1] order by column2) 

高斯、达梦都支持

1、根据 column1 分组，在分组内部根据 column2 排序，返回每组内部排序后的顺序编号（此行号组内连续且唯一)

2、不加 partition by column1 时，表示只排序不分组。

3、特别提醒：select 语句执行顺序：from ...> where ...> group by ...> having ... > select ...> order by ...

使用举例

--查询所有人员，工资由高到低排序，第一列(xh)是行号，从1开始且连续。

select row_number() over(order by t.sal desc) as xh , t.* from emp t ;

--查询所有人员，部门内部员工的工资由高到低排序

select row_number() over(partition by t.deptno order by t.sal desc) as xh , t.* from emp t ;

--查询每个部门中工资最高的人 （这里只是介绍用法，实际中可能存在工资一样的人，此时需要使用 dense_rank() over ）

select t2.* from ( select row_number() over(partition by t.deptno order by t.sal desc) as xh , t.* from emp t ) t2 where t2.xh <=1;

-- 使用 row_number() over 分组排序方式进行分页（只需要嵌套1层子查询）
select t2.* from (select row_number() over(order by t1.empno desc) as xh,t1.* from emp t1) t2 where t2.xh between 6 and 12;

-- 使用 rownum 方式分页，查询员工数据，根据员工编号倒序排序（需要嵌套2层子查询）
select * from (select rownum xh,t2.* from (select * from emp t1 order by t1.empno desc) t2) t3 where t3.xh between 6 and 12;

rank() over 跳级分组排名

dense_rank() over() 连续分组排名

1、rank() over、dense_rank() over()，用法和格式与 row_number() over 完全一样，区别如下：

--查询所有人员，按工资的由高到低排序，不支持并列排名
select row_number() over(order by t.sal desc) as xh , t.* from emp t ;
---查询所有人员，按工资的由高到低排序，支持并列排名，且排名不会跳级
select dense_rank() over(order by t.sal desc) as xh , t.* from emp t ;
---查询所有人员，按工资的由高到低排序，支持并列排名，但是排名会跳级
select rank() over(order by t.sal desc) as xh , t.* from emp t ;

max|min() keep (dense_rank first|last order by ...) 取排序后第1条或最好1条的值

1、Oracle 专属的高级聚合函数，核心作用：在分组内，按指定字段排序后，取最后 / 第一条记录的某个字段值，比子查询 / 窗口函数更简洁高效。

2、性能优势：比 ROW_NUMBER() + 子查询 更快，无需嵌套

3、高斯、达梦都支持，语法格式如下：

-- 取【排序后最后一条】记录的 字段值，找不到时，值为null
MAX(字段) KEEP (DENSE_RANK LAST ORDER BY 排序字段 [desc])

-- 取【排序后第一条】记录的 字段值，找不到时，值为null
MIN(字段) KEEP (DENSE_RANK FIRST ORDER BY 排序字段 [desc])

1、order by 排序字段 [desc]：按指定字段排序，和普通查询语句一样，可以多个字段排序，默认升序，可以降序。
2、first | last：取排序后的第一个值/最后一个值的记录。
3、max | min (字段) ：取指定字段的最大 | 最小值
4、可以配合 group by 使用，达到分组排序的同时，直接取第一条或者最后一条的指定值。
5、遇到排序值相同的记录，会保留所有并列记录，所以外层必须用 max/min 收敛成一个值。比如按部门排序，一个部门就会有多个人。
SELECT t.deptno,
       --部门中工资最低的薪水
       (MIN(sal) KEEP(DENSE_RANK FIRST ORDER BY t.sal)) min_sal,
       --部门中工资最高的薪水
       (MAX(sal) KEEP(DENSE_RANK LAST ORDER BY t.sal)) max_sal,
       --部门中最早入职员工的最低薪水(排序后是可能有多条数据的，因为同一天入职的人不止1个，所以 min、max就可以用上)
       (MIN(sal) KEEP(DENSE_RANK FIRST ORDER BY t.HIREDATE)) min_sal2
  FROM emp t
 GROUP BY t.deptno;

-- 部分录入视图：row_span(跨行)、col_level（级次） 全部是1，没有级次关系，parent_id（父节点ID）也不正确
-- 获取它正确的 parent_id 值：此时全部作为两级表头进行处理，Field_Code 为空的作为1级，不为空的作为2级
SELECT T.Field_Code,
       t.title,
       t.row_span,
       t.col_span,
       t.col_level,
       t.is_visible,
       t.id,
       t.parent_id,
       -- 找当前节点上级：排序在自己前面且 field_code 为空的就是它的父级.(没有时值为null)
       (SELECT MAX(p.id) KEEP(DENSE_RANK LAST ORDER BY p.disp_order)
          FROM gap_ui_column p
         WHERE p.view_id = t.view_id
           AND t.field_code IS NOT NULL
           AND p.field_code IS NULL
           AND p.disp_order < t.disp_order) new_parent_id
  FROM gap_ui_column t
 WHERE view_id = '2022070601Hn4'
 ORDER BY t.disp_order;
-- 部分视图：row_span(跨行)、col_span（跨列）、col_level（级次）配置正常，有级次关系，parent_id 不正确
-- 获取它正确的 parent_id 值：按着级次关系进行查找，查找上一级中 field_code 为空，且排序在自己前面第一个值
SELECT T.Field_Code,
       t.title,
       t.row_span,
       t.col_span,
       t.col_level,
       t.is_visible,
       t.id,
       t.parent_id,
       -- 找当前节点上级：上一级中，排序在自己前面且 field_code 为空的就是它的父级.(没有时值为null)
       (SELECT MAX(p.id) KEEP(DENSE_RANK LAST ORDER BY p.disp_order)
          FROM gap_ui_column p
         WHERE p.view_id = t.view_id
           AND p.col_level < t.col_level
           AND p.disp_order < t.disp_order) new_parent_id
  FROM gap_ui_column t
 WHERE view_id = 'c18a569bf4f749a9a0b440294471e55aHn4'
 ORDER BY t.disp_order;

列转行

wm_concat

1、wm_concat 函数oracle12g版本开始不支持，Oracle 官方更推荐使用下面的 listagg() within group 函数。

达梦、高斯也支持 wm_concat 函数

-- 1、查询员工表中的所有员工姓名，并转为一行，用 "," 隔开，如: 张三,李四,Jock
select wm_concat(ename) as names from emp t;
-- 2、按部门编号分组查看每个部门中的员工姓名
select deptno,wm_concat(ename) as names from emp group by deptno;

-- 3、通过Oracle 系统视图查询 emp 表的所有字段，如：EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO

--这个操作还是非常有用的，比如一个表有几十个或者上百个字段，select x,y,z ...，如果靠人工一个个写，太累而且容易出错
select wm_concat(t.COLUMN_NAME) from user_tab_columns t where t.TABLE_NAME = 'EMP';

listagg() within group

1、Oracle 官方更推荐使用 listagg 函数，格式：listagg(column_nname，delimiter) [within group (order by order_by_column)];

达梦兼容，高斯开启了 Oracle 兼容模式时也兼容。

column_nname：待转换的列名，如 ename

delimiter：分隔符，默认为 null，无分隔符

order_by_column：指定列值拼接顺序，如 order by ename

listagg 是必须的，排序的 within group 是可选的！

 2、listagg 函数使用举例如下：

--查询所有员工的姓名，用 "+" 号分割，如：SMITH+张三+WARD+JONES+MARTIN+李四+MILLER
select listagg(t.ename,'+') as names from emp t ;
--分组查看各部门的员工姓名，使用 "," 分割，姓名按薪水高低排列拼接
select t.deptno,listagg(t.ename,',') within group(order by t.sal desc) as neames from emp t group by t.deptno;
--查询指定表的所有字段,对于想要快速获取某个表的字段是非常有用的操作
select listagg(t.COLUMN_NAME,',') within group(order by t.COLUMN_ID) from user_tab_columns t where t.TABLE_NAME = 'EMP';

高斯-STRING_AGG

1、GaussDB 开启了 Oracle 兼容模式时，listagg 函数是兼容的，可以直接执行，但是对于应用程序来说，和驱动数据源有关系，比如 Druid 通过 org.postgresql.Driver 自动识别 dbType=postgresql，加载 PG 语法解析器，PG 解析器不认 LISTAGG 写法。这样就出现同一个 sql 手动在库里面可以执行，但是程序执行就报错。

2、此时可以使用高斯自己的 STRING_AGG 代替（只能高斯自己使用）。

select t.deptno, (STRING_AGG(t.ename,',' order by t.sal desc )) as  neames from emp t group by t.deptno;
select t.deptno, listagg(t.ename,',') within group(order by t.sal desc) as neames from emp t group by t.deptno;

xmlagg wellformed

1、xmlagg 函数解决listagg函数字符串拼接过长问题，返回 clob 类型。

xmlagg 函数格式：

select 需要分组的字段,
       xmlagg(xmlparse(content 需要行转列合并展示的字段 || ';' wellformed) order by 排序字段)
       .getclobval()
  from 表名
 group by 需要分组的字段;

--分组查看各部门的员工姓名，使用 "," 分割，姓名按薪水高低排列拼接
select t.deptno,
       rtrim(xmlagg(xmlparse(content t.ename || ',' wellformed) order by t.sal desc)
             .getclobval(),
             ',')
  from emp t
 group by t.deptno;

行转列

case when

--行转列 case when 方式：统计每年入职的人数
--    	1980  1981	1982	1983	1984
--    	1	1	10	2	1	0
select 
       sum(case when to_char(t.hiredate, 'yyyy') = '1980' then 1 else 0 end) as "1980",
       sum(case when to_char(t.hiredate, 'yyyy') = '1981' then 1 else 0 end) as "1981",
       sum(case when to_char(t.hiredate, 'yyyy') = '1982' then 1 else 0 end) as "1982",
       sum(case when to_char(t.hiredate, 'yyyy') = '1983' then 1 else 0 end) as "1983",
       sum(case when to_char(t.hiredate, 'yyyy') = '1984' then 1 else 0 end) as "1984"
  from emp t
 where hiredate is not null;

decode

--行转列 decode 方式：统计每年入职的人数
--    	1980	1981	1982	1983	1984
--    	1	1	10	2	1	0
select sum(decode(to_char(t.hiredate, 'yyyy'), '1980', 1, 0)) as "1980",
       sum(decode(to_char(t.hiredate, 'yyyy'), '1981', 1, 0)) as "1981",
       sum(decode(to_char(t.hiredate, 'yyyy'), '1982', 1, 0)) as "1982",
       sum(decode(to_char(t.hiredate, 'yyyy'), '1983', 1, 0)) as "1983",
       sum(decode(to_char(t.hiredate, 'yyyy'), '1984', 1, 0)) as "1984"
  from emp t
 where hiredate is not null;

 pivot

1、pivot 函数实现将指定字段的字段值转换为列。

2、格式：select * from 表名 pivot(聚合函数(需要按行展示的字段) for 需要转列展示的字段 in ('内容1' AS 字段别名1,'内容2' AS 字段别名2,'内容n' AS 字段别名n));

--行转列 pivot 方式：统计每年入职的人数
--注意点1：外层 select 无法直接指定具体的列，且除了 pivot for 后面中的列最为最终的列之外-
--其它在 privot for 中没有体现的列，默认也会展示在最终的结果中，所以通常需要借助子查询提前过滤好列信息
select *
  from (select to_char(t.hiredate, 'yyyy') year, count(1) total
          from emp t
         group by to_char(t.hiredate, 'yyyy')) t2
pivot(sum(t2.total)
--注意点2：for 后面的列名不能用别名，否则报错：ORA-01748:此处只允许简单的列名
   for year in(1980, 1981, 1982, 1983, 1984));

--行转列 pivot 方式：统计每个部门的人数
select *
  from (select t.deptno from emp t where t.deptno is not null)
pivot(count(1)
   for deptno in(10 as "10_ACCOUNTING",
                 20 as "20_RESEARCH",
                 30 as "30_SALES",
                 40 as "40_OPERATIONS"));

分组统计

1、项目中经常会遇到分组统计的需求，以下写法仅供参考。

数据准备：sql/oracle/分组统计.sql · 汪少棠/material - Gitee.com。

 --方式3——推荐
 SELECT 
  agency_code,
  fiscal_year,
  sum((case when ui_code = '202005' and per_sta_code='1' then 1 else 0 end)) as 行政在职人员汇总,
  sum((case when ui_code = '202005' and per_sta_code='2' then 1 else 0 end)) as 行政退休人员汇总,
  sum((case when ui_code = '202005' and per_sta_code='3' then 1 else 0 end)) as 行政离休人员汇总,
  sum((case when ui_code = '202006' and per_sta_code='1' then 1 else 0 end)) as 事业在职人员汇总,
  sum((case when ui_code = '202006' and per_sta_code='2' then 1 else 0 end)) as 事业退休人员汇总,
  sum((case when ui_code = '202006' and per_sta_code='3' then 1 else 0 end)) as 事业离休人员汇总,
  sum((case when ui_code = '202005' and per_sta_code='1' then nvl(basic_mon,0) + nvl(achievement_bonus, 0) else 0 end)) as 行政在职工资汇总,
  sum((case when ui_code = '202005' and per_sta_code='2' then nvl(basic_mon,0) + nvl(achievement_bonus, 0) else 0 end)) as 行政退休工资汇总,
  sum((case when ui_code = '202006' and per_sta_code='1' then nvl(basic_mon,0) + nvl(achievement_bonus, 0) else 0 end)) as 事业在职工资汇总,
  sum((case when ui_code = '202006' and per_sta_code='2' then nvl(basic_mon,0) + nvl(achievement_bonus, 0) else 0 end)) as 事业退休工资汇总
FROM person_info_wmx group by agency_code, fiscal_year 
order by agency_code, fiscal_year;

cast 数据类型转换

1、cast() 函数用于转换数据类型，格式：cast(列名/值 as 目标数据类型)，值为 null 时不会有影响，转换后的值也为null。 

2、使用举例如下：

 -- 字符串转数值，a1= 224 , a2的值为 null , a3的值为 null
select cast('124' as number) + 100 as a1, cast('' as number) a2, cast(null as number) a3 from dual;

select concat(cast(t.sal as varchar2(32)),'00') as str from emp t; --数值转字符串，sal 值为 null 时，不会有影响。
--浮点型精度截断：emp.sal 原本类型是 number(7,2) ，转换之后为 number(18,0) 表示小数位数为 0，结果就是对 sal 字段的值取整，且四舍五入，如 2850.55 -> 2851
select cast(t.sal as number(18,0)) as str from emp t;
select cast(t.sal as number(18,4)) as str from emp t; --浮点型精度截断：同理也可以增加精度，如：2850.55 -> 2850.5500

-- N1= -0.86   N2 =   -1000.86  N3 =  2000.00
select cast(-0.8596 as number(18, 2)) n1,  cast('-1000.8596' as number(18, 2)) n2,  cast(2000 as number(18, 2)) n3 from dual;

ORA-00932:数据类型不一致:应为CHAR,但却获得 BINARY。可以使用cast函数统一结果的数据类型。

dbms_random 生成随机数

Oracle 官网 dbms_random 文档：DBMS_RANDOM

生成随机数字

dbms_random.random 用于生成正负整数
select dbms_random.random as random from dual;   -- 生成一个10位数的正负随机数，如 -765136417、1703191771
-- 生成一个10位数的正随机数，使用绝对值函数辅助，如 228821755、1542114642
select abs(dbms_random.random) as random from dual;
select abs(mod(dbms_random.random,100)) as random from dual;  -- 生成一个 100 以内的随机正数，使用取余函数+绝对值函数辅助
select 100 + abs(mod(dbms_random.random,999)) as random from dual;  -- 生成一个 100—999 的随机整数
dbms_random.value 用于生成随机小数
select dbms_random.value as random from dual; -- 生成一个 0～1 之间随机小数，精确到小数点后面 15 位，如 0.623657615937181
-- 生成一个 10—100 之间的随机小数，精确到小数点后面 15 位，如 14.2489446249136
select dbms_random.value(10,100) as random from dual;
-- 生成一个 10—9999之间的随机小数，精确到小数点后面 2 位，如 6538.26
select round(dbms_random.value(10,9999),2) as random from dual;
-- 生成一个100～1000之间的随机整数，使用 trunc 小数截断函数赋值，强行去掉小数位
select trunc(100 + 900 * dbms_random.value, 0) as random from dual;
-- 生成一个[10,100)之间的随机整数
select trunc(dbms_random.value(10,100)) as random from dual;
-- 生成一个由数字组成的 16 位的随机字符串，使用 cast 函数辅助，以 '.' 开头，如：.980565525834441。（注意最长也只能是 39 位）
select cast(dbms_random.value as varchar2(16)) from dual;
-- 生成一个由数字组成的 32 位的随机字符串，使用 substr、cast 函数辅助，如：74899075116317540687814596072324
select substr(cast(dbms_random.value as varchar2(39)),2,32) as random from dual ;

生成随机字符串

格式：dbms_random.string(opt, length)

opt 可取值：'u' 或者 'U'  :大写字母、'l' 或者 'L' : 小写字母、'a' 或者 'A' :  大小写字母、'x' 或者 'X' :  数字与大写字母、'p' 或者 'P'  : 可打印字符。

length：生成的字符串长度

 select dbms_random.string('x',20) from dual;   -- 生成如： BRFSJLU79P909RLZ7FJI

 select dbms_random.string('P',20) from dual;  -- 生成如： E}"-!GS{w,/H?PT$}y&l

select sys_guid() from dual;   --生成 32 位的 guid 随机字符串，如：AE2AE001C4BC4C259CBC8CFF443E5801

从表中获取随机数据

select *  from emp  order by dbms_random.random;    --查询所有员工，并随机排序输出

--从员工表中随机或取 4 个员工数据

select * from ( select *  from emp  order by dbms_random.random)  where rownum < 5;

sys_guid() 生成 UUID

1、Java JDK 有 API 可以生成 UUID，Oracle 中也有相应的方法可以生成 —— sys_guid()，可以很方便的防止主键冲突。

2、正常的 UUID 是 36 位的(包括其中的4个"-")，而 sys_guid() 生成的不带 "-"，默认是 32 位。

select sys_guid() from dual; -- 生成 32 位的 GUID，大写字母+数字，如：9B654FEF9D1D43BDB05406699049CA26
select lower(sys_guid()) from dual;-- 生成 32 位的 GUID，小写字母+数字，如：6f8f179015874321a8599143e38d37b6

-- 随机生成 100 个UUID
declare
 total number := 100;
begin
   dbms_output.put_line('========随机生成' || total ||' 个UUID值========');
  for i in 1..total loop
    dbms_output.put_line(i || '===>' || sys_guid());
  end loop;
end;
create table STUDENT2 (
  stuid   VARCHAR2(38) not null,
  stuname VARCHAR2(10) not null
);
insert into STUDENT2(stuid,stuname) values(sys_guid(),'张三');-- 插入数据，stuid 使用 GUID 随机生成
insert into STUDENT2(stuid,stuname) values(dbms_random.string('x',20),'老四');-- 使用20位的随机字符串作为 stuid 的值

unistr 将 Unicode 字符串解码

1、程序里面将字符串转为 unicode 的值通常是这样的：Hi，我是 123。jackson. -> Hi\uff0c\u6211\u662f 123\u3002jackson.

2、将转为 unicode 后的结果存储到数据之后，在 Oracle 中可以使用 unistr 函数进行解码。

--解码结果为：Hi，我是 123。jackson.
select unistr(REPLACE('Hi\uff0c\u6211\u662f 123\u3002jackson.','\u','\')) from dual;
-- 生僻字 存储表解码
SELECT T.*,unistr(replace(t.unicode_char,'\u','\')) 解码 FROM BAS_UNUSUAL_CHARACTERS T; 

--获取其中连续的中文内容，结果是：大家好
select regexp_substr('hello,大家好！我是蚩尤后裔，电话 123', '[' || unistr('\0391') || '-' || unistr('\9fa5') || ']+') from dual;
--获取其中全部的中文内容，结果是：大家好我是蚩尤后裔电话
select regexp_replace('hello,大家好！我是蚩尤后裔，电话 123', '[^' || unistr('\0391') || '-' || unistr('\9fa5') || ']','') from dual;

3、封装为自定义函数：

create or replace function unicode2Str(encode_text in varchar2)
  return nvarchar2 is
  -- 对 unicode 内容进行解码
  v_sql    varchar2(100);
  v_result nvarchar2(100);
begin
  v_sql := 'select unistr(''' || REPLACE(encode_text, '\u', '\') ||  ''') from dual ';
  execute immediate v_sql into v_result;-- 将执行的结果赋给 v_result 返回
  return v_result;

  exception
    when others then
    --如果发生异常：ORA-30186: ''\'' 的后面必须为四个十六进制的字符或另一个 ''\''
    --则原样返回内容
    return encode_text;
end;

--函数调用
SELECT unicode2Str('Hi\ff0c\u6211\u662f 123\u3002jackson.') FROM dual T;

UTL_URL.UNESCAPE - URL解码

1、格式：UTL_URL.UNESCAPE(msg, 'UTF8') 

2、表单 URL 编码会把空格编码成`+`，而不是`%20`，此时要先 replace。

3、`UTL_URL` 包需要执行权限，如果报权限不足，DBA 执行: GRANT EXECUTE ON UTL_URL TO 你的用户;

4、注意: 这个写法**不走索引**，是函数扫描。如果数据量大，建议 方案 A：应用层解码后传参；方案 B：建立函数索引。
    

SELECT UTL_URL.UNESCAPE('%E6%80%A7%E8%83%BD','UTF8') AS res FROM DUAL;
SELECT UTL_URL.UNESCAPE(REPLACE('%E6%80%A7%E8%83%BD', '+', ' '), 'UTF8') AS res FROM DUAL;

Logo
乐奇 Rokid 开放社区

更多推荐

AI 智能眼镜的柔性神经，开启轻量化人机交互新世代

AI 智能眼镜的核心诉求，是在接近传统眼镜的轻薄体积内，塞进 AI 主控芯片、摄像头、麦克风阵列、骨传导 / 扬声器、光波导显示模组、触控传感器、电池、蓝牙 / Wi-Fi 射频模块等大量器件。随着 AI 眼镜出货量快速上行，鹏鼎、弘信、则成等国内 FPC 厂商纷纷布局 AI 眼镜软板、软硬结合板量产方案，单机 FPC 价值量显著高于 TWS 耳机，带动国内柔性线路板供应链升级，倒逼国内厂商突破超

avatar 乐奇 Rokid 开放社区

听书阁：让你在 AR 眼镜上也可以读小说

戴上眼镜之后我才发现，"翻页"是个需要用手完成的动作。手机上有触屏，滑动、点按、长按选词，一整套肌肉记忆。到了 AR 眼镜上这些全没了：视野只有一小块，手上可能还拎着东西，能稳定输入的只剩嘴和眼镜腿上的几颗按键。把手机阅读器原样搬过来没有意义，得重新想一遍"读一本书"在眼镜上该拆成哪几个动作。听书阁就是这次重想的产物。它跑在 Rokid Craft 上，用 AIUI 的 Ink 写页面，四个页面加

avatar 乐奇 Rokid 开放社区
cover

一期一会 | 什么是抬头显示器（HUD）？

avatar 乐奇 Rokid 开放社区