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
图例:
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
图例:
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
图例:
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
图例:
2、将数据按照某字段进行分组均分
场景:根据某个字段进行分组之后进行跳跃排序
语法:NTILE(分区数量) OVER(ORDER BY 分区依据) mm
样例:
SELECT
ID,
NTILE(3) OVER(ORDER BY COLUMN3) mm
FROM DEMO
图例:
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 分组条件
图例:
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
图例:
2、行转列
语句:PIVOT
语法:PIVOT(聚合函数 for 需要转化的列 in (值)
样例:
SELECT * FROM DEMO
PIVOT
(
SUM(COLUMN4) FOR
COLUMN1 IN ('t1','t2','t3')
)
图例:
3、列转行
语句:UNPIVOT ()
语法:select … from … UNPIVOT(列名,…)
样例:
SELECT * FROM DEMO
UNPIVOT (
tmp FOR TEST IN (COLUMN1,COLUMN2)
)
图例:
九、资源
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');