Oracle 报表常用sql

Oracle 报表常用sql

数据库建模式、用户、表空间、表、数据语句在最末尾


一、数据类型转化

函数:translate(字段 USING NCHAR_CS)

样例

SELECT translate(COLUMN1  USING NCHAR_CS)  FROM DEMO WHERE 1=1

函数:cast(字段 as nvarchar2(10))

样例

SELECT cast(COLUMN1  as nvarchar2(10))   FROM DEMO WHERE 1=1

二、字符串操作

1、字符串拼接

1.1、|| 拼接

语法:字段or字符串||字符串

样例

select 'a'||'b' from dual;
1.2、CONCAT()拼接

语法:concat(‘a’,‘b’)

样例

select concat('a','b') from dual;

PS:多个字符串需要嵌套concat

1.3、字符串分组拼接,指定拼接符

语法:listagg(连接字段,‘,’)… group by 分组字段

PS:listagg分组时常会根据某个字段排序,保证拼接有效性

样例

SELECT
	COLUMN1,
	LISTAGG(COLUMN3 ,'-') WITHIN GROUP(ORDER BY ID) 
FROM
	DEMO
where  1=1
GROUP BY COLUMN1 ORDER BY COLUMN1 ASC 

图例
image-20220716171858229

2、字符串截取

语法:SUBSTR()

样例

SELECT SUBSTR('0123456789', 1) FROM dual     --截取所有字符串   '0123456789'
SELECT SUBSTR('0123456789', 2) FROM dual     --从第2个字符开始  '123456789'
SELECT SUBSTR('0123456789', -2) FROM dual    --从倒数第2个字符  '89'
SELECT SUBSTR('0123456789', 2, 3) FROM dual  --第2个字符开始,截取3个字符   '123'
SELECT SUBSTR('0123456789', -4, 3) FROM dual --倒数第4个字符开始,截取3个字符 '678'		

3、字符串查找

语法:INSTR()

样例

--表示从字符开始查找第1次出现的位置
SELECT INSTR('aabbccddeeffgghhiiggffeeddcc', 'ff') FROM dual    --返回11

 --表示从开始查找子字符串第1次出现的位置
SELECT INSTR('aabbccddeeffgghhiiggffeeddcc', 'ff', 5) FROM dual --返回11
 
--表示从第5个字符开始查找子字符串第1次出现的位置
SELECT INSTR('aabbccddeeffgghhiiggffeeddcc', 'ff', 5) FROM dual    --返回11
 
--表示从第5个字符开始查找子字符串第1次出现的位置
SELECT INSTR(''aabbccddeeffgghhiiggffeeddcc'', 'ff', 5, 1) FROM dual    --返回11
 
--表示从第3个字符开始查找子字符串第2次出现的位置
SELECT INSTR('aabbccddeeffgghhiiggffeeddcc', 'ff', 3, 2) FROM dual    --返回21

4、字符串次数统计

函数:REGEXP_COUNT(列,‘统计字串’)

样例

SELECT REGEXP_COUNT(COLUMN2,'a') FROM DEMO WHERE 1=1

5、正则替换

语法:replace()

样例

select replace(regexp_replace('123null?·123123“\123','[(null)?*·*“*]+',''),'\','\\') from dual;

三、日期操作

1、获取年、月、季、时、分、秒

语法:to_char(字段, ‘yyyy-MM-dd HH:mm:ss’)

样例

SELECT to_char(SYSDATE, 'yyyy'),
to_char(SYSDATE, 'q'),
to_char(SYSDATE, 'MM'),
to_char(SYSDATE, 'WW') 周每年,
to_char(SYSDATE, 'dy'),
to_char(SYSDATE, 'dd'),
to_char(SYSDATE, 'DD') 日每年,
to_char(SYSDATE, 'HH'),
to_char(SYSDATE, 'mm'),
to_char(SYSDATE, 'ss'),
to_char(SYSDATE, 'SSSS') 豪秒
FROM dual

图例

image-20220717000157465

2、字符串转日期

语法:to_date()

样例

to_date('2022-07-17','yyyy-mm-dd')

四、循环条件

语法:for i in 列 loop…end loop;

样例:

SELECT * FROM DEMO;
BEGIN 
	for row_test in (SELECT * FROM DEMO) loop
	    sys.dbms_output.put_line(DEMO.ID);
	  end loop;
END;

五、分支条件

1、CASE WHEN…THEN…语句

语法:CASE WHEN 条件 THEN 结果

或者:CASE 字段 WHEN 条件 THEN 结果

样例

SELECT
	ID,
	(case
		WHEN COLUMN1='t1' THEN '测试11'
		WHEN COLUMN1='t2' THEN '测试22'
		WHEN COLUMN1='t3' THEN '测试33'
	ELSE '其他' END) detail
FROM
	DEMO d 
where  1=1

/**or**/
	(CASE COLUMN1
		WHEN 't1' THEN '测试11'
		WHEN 't2' THEN '测试22'
		WHEN 't3' THEN '测试33'
	ELSE '其他' END) detail

2、decode ()语句

语法:decode (条件, 值1, 返回值1, 值2, 返回值2,…值n,返回值n,缺省值)

样例

SELECT
	ID,
	decode(COLUMN2,'a1','1111', COLUMN2,'a2','2222') mm
FROM
	DEMO
where  1=1

六、统计函数

1、分组求和赋值每列

场景:根据某个字段分组后将求和后值依次给每一列

语法:SUM(求和列) OVER(PARTITION BY 分组列) mm

样例

SELECT 
ID,
SUM(COLUMN3) OVER(PARTITION BY COLUMN1) mm
FROM DEMO

图例

image-20220716155658074

2、分组连续求和赋值每列

场景:根据某个字段分组根据排序依次赋值给每一列

语法:SUM(求和列) OVER(PARTITION BY 分组列 ORDER BY 排序 ASC) mm

样例

SELECT 
ID,
SUM(COLUMN3) OVER(PARTITION BY COLUMN1 ORDER BY ID ASC) mm
FROM DEMO

图例

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zz2b0R43-1658041329591)(https://oss.wslhome.top/markdown/2022/07/16/image-20220716155545811.png)]

2、补充函数

函数说明
SUM(…)求和函数
COUNT(…)统计函数
avg()求平均
max()求最大值
min()求最小值
variance()方差

七、排序函数

1、分组跳跃排序

场景:根据某个字段进行分组之后进行跳跃排序

语法:rank() OVER(PARTITION BY 分组条件,… ORDER BY 排序条件) mm

样例

SELECT 
ID,
rank() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2) mm
FROM DEMO

图例
image-20220716155545811

2、将数据按照某字段进行分组均分

场景:根据某个字段进行分组之后进行跳跃排序

语法:NTILE(分区数量) OVER(ORDER BY 分区依据) mm

样例

SELECT 
ID,
NTILE(3) OVER(ORDER BY COLUMN3) mm
FROM DEMO

图例

image-20220716160529941

3、补充函数

函数说明
row_number() OVER(…)依次排序
rank() OVER(…)跳跃排序(相同,会占位)
dense_rank() OVER(…)连续排序
NTILE(…) OVER(…)均分分组排序

PS :

函数:CUBE()

说明:等于对全表进行group by

用法: GROUP BY CUBE (…)


八、行列操作

1、分组拼接

语句:listagg() WITHIN GROUP ()

语法:listagg(合并字段,拼接符号) WITHIN GROUP (order by 依靠什么字段合并)

样例

select  xxx,
listagg(B,',') within group (order by A) Bstr
  from table
  where 1=1
  group by 分组条件

图例

image-20220716232214147

2、一行拆分为多行

语句:REGEXP_SUBSTR()

语法:distinct REGEXP_SUBSTR(拆分字段,正则,开始,结束)…connect by …

样例

SELECT distinct REGEXP_SUBSTR('A,B,C,D','[^,]+',1, level) test,t.*
FROM DEMO t
connect by level <= regexp_count('A,B,C,D', ',') + 1

图例

image-20220716233106972

2、行转列

语句:PIVOT

语法:PIVOT(聚合函数 for 需要转化的列 in (值)

样例

SELECT * FROM DEMO
PIVOT 
(
 SUM(COLUMN4) FOR 
 COLUMN1 IN ('t1','t2','t3')
) 

图例

image-20220716231908249

3、列转行

语句:UNPIVOT ()

语法:select … from … UNPIVOT(列名,…)

样例

SELECT * FROM DEMO 
UNPIVOT (
  tmp FOR TEST IN (COLUMN1,COLUMN2)
)

图例
image-20220716230741153

九、资源

在这里插入图片描述
在这里插入图片描述在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

PS:上图来源于博客Oracle常用函数总结_Seven_0507的博客

开源协议: CC 4.0 BY-SA 版权协议

连接:https://blog.csdn.net/KEEP_GIONG/article/details/80089703


零、建表sql

进行学习之前请准备好oracle,创建好对用户、模式、应表进行学习

建立用户、模式、表空间语句

-- 移除表空间
DROP TABLESPACE DEMO INCLUDING CONTENTS AND DATAFILES

-- 移除用户
drop user DEMO CASCADE

-- 创建表空间
CREATE TABLESPACE DEMO
DATAFILE 'D:/DEMO.dbf' 
SIZE 1G AUTOEXTEND ON next 1G
-- 实际应用
create user DEMO identified by 123456
default tablespace DEMO
quota unlimited on DEMO
-- 权限设设置
grant create session to DEMO;
grant create table to DEMO;
grant create tablespace to DEMO;
-- 需要登陆之后才能使用

-- 查看当前用户的权限
select * from role_sys_privs;

建立表、数据

-- DEMO.DEMO definition

CREATE TABLE "DEMO" 
   (	"ID" NUMBER(38,0), 
	"COLUMN1" VARCHAR2(100), 
	"COLUMN2" VARCHAR2(100), 
	"COLUMN3" NUMBER(38,0), 
	"COLUMN4" VARCHAR2(100)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DEMO" ;

CREATE INDEX "DEMO_ID_IDX" ON "DEMO" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DEMO" ;
  CREATE INDEX "DEMO_COLUMN1_IDX" ON "DEMO" ("COLUMN1") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DEMO" ;
  CREATE INDEX "DEMO_COLUMN3_IDX" ON "DEMO" ("COLUMN3") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DEMO" ;

数据:

INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(1, 't1', 'a1', 10, '1');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(2, 't1', 'a1', 20, '2');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(3, 't1', 'a2', 30, '3');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(4, 't1', 'a2', 40, '4');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(5, 't1', 'a3', 50, '5');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(6, 't2', 'a3', 60, '1');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(7, 't2', 'a1', 70, '2');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(8, 't2', 'a1', 80, '3');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(9, 't2', 'a2', 90, '4');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(10, 't2', 'a2', 100, '5');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(11, 't3', 'a3', 110, '1');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(12, 't3', 'a3', 120, '2');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(13, 't3', 'a4', 130, '3');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(15, 't3', 'a5', 140, '5');
INSERT INTO DEMO
(ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES(16, 't3', 'a5', 150, '6');

  • 3
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

sirwsl

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值