【Oracle】常用、实用的SQL脚本(持续更新)

avatar
作者
筋斗云
阅读量:13

【Oracle】常用、实用的SQL脚本(持续更新)

受到一篇知乎文章启发,决定将之前用到和看过的一些SQL脚本收集下,这些脚本在工作中都比较实用,并且也常用到。脚本都是针对Oracle的。

先创建几个用来举例的表,并插入些数据

CREATE table sqltest
(id int,name varchar(20),quarter int,cum int)
insert into sqltest values(1,N'苹果',1,1000);
insert into sqltest values(1,N'苹果',2,2000);
insert into sqltest values(1,N'苹果',3,4000);
insert into sqltest values(1,N'苹果',4,5000);
insert into sqltest values(2,N'梨子',1,3000);
insert into sqltest values(2,N'梨子',2,3500);
insert into sqltest values(2,N'梨子',3,4200);
insert into sqltest values(2,N'梨子',4,5500);
select * from sqltest
create table xiaoliang(emp number,riqi DATE,xiaoliang number);
insert into xiaoliang values(1,to_date('2019-01-01','yyyy-mm-dd'),60);
insert into xiaoliang values(1,to_date('2019-01-02','yyyy-mm-dd'),70);
insert into xiaoliang values(1,to_date('2019-01-03','yyyy-mm-dd'),71);
insert into xiaoliang values(1,to_date('2019-01-04','yyyy-mm-dd'),50);
insert into xiaoliang values(1,to_date('2019-01-05','yyyy-mm-dd'),80);
insert into xiaoliang values(2,to_date('2019-01-01','yyyy-mm-dd'),80);
insert into xiaoliang values(2,to_date('2019-01-02','yyyy-mm-dd'),89);
insert into xiaoliang values(2,to_date('2019-01-03','yyyy-mm-dd'),70);
insert into xiaoliang values(2,to_date('2019-01-04','yyyy-mm-dd'),90);
insert into xiaoliang values(2,to_date('2019-01-05','yyyy-mm-dd'),75);
create table chengji(sno number,km varchar2(10),score number);
insert into chengji values(1,'YW',60);
insert into chengji values(1,'SX',60);
insert into chengji values(1,'YY',60);
insert into chengji values(2,'YW',70);
insert into chengji values(2,'SX',70);
insert into chengji values(2,'YY',80);
insert into chengji values(3,'YW',80);
insert into chengji values(3,'SX',70);
insert into chengji values(3,'YY',80);
insert into chengji values(4,'YW',80);
insert into chengji values(4,'SX',90);
insert into chengji values(4,'YY',90);

一,同组多行字段数据合并,可排序后再合并

用到sqltest,这个表举例子

---1,使用LISTAGG与group by 
SELECT  
id, 
name,
LISTAGG(cum, ',') WITHIN GROUP (ORDER BY quarter)  quarter_cum
from sqltest t1
group by id,name

--2,使用LISTAGG与LISTAGG与group by 合并后返回多行,使用distinct去重
SELECT
distinct  
id, 
name, 
LISTAGG(cum, ',') WITHIN GROUP (ORDER BY quarter desc)  over(PARTITION BY name) quarter_cum
from sqltest t1

在这里插入图片描述

使用distinct去重后

在这里插入图片描述

--3,使用wm_concat函数
select
id,
name,
to_char(wm_concat(cum)) quarter_cum
from sqltest
group by id,name

使用wm_concat函数,没有LISTAGG那么灵活,LISTAGG可以排序后再合并,而且可以自定义分隔符号,wm_concat固定是使用‘,’做分隔符,只能使用relpace进行替换。

二,既然可以多行合并成一个字段,当然也可以将一个字段拆分行显示

--使用合并后的数据作为测试
with sale_data as (
SELECT  
id, 
name,
LISTAGG(cum, ',') WITHIN GROUP (ORDER BY quarter)  quarter_cum
from sqltest t1
group by id,name
)
--REGEXP_SUBSTR 递归截取合并后的字段,因为是递归截取,所以会出现重复的数据,使用distinct去重
SELECT distinct        
t.id,
t.name,
level as jidu,
REGEXP_SUBSTR(quarter_cum, '[^,]+', 1, level) cum
from sale_data t
connect by level <= regexp_count(quarter_cum, '[^,]+')
 order by t.id, t.name;

在这里插入图片描述

REGEXP_SUBSTR函数格式如下:

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr :需要进行正则处理的字符串

__pattern :进行匹配的正则表达式

__position :起始位置,从第几个字符开始正则表达式匹配(默认为1)

__occurrence :标识第几个匹配组,默认为1

__modifier :模式('i’不区分大小写进行检索;‘c’区分大小写进行检索。默认为’c’。)

还有其他方法:

三,行与列相互转换

1,列转行

--使用pivot 列转行
select 
id,
name,
"1" as 第一季度,
"2" as 第二季度,
"3" as 第三季度,
"4" as 第四季度
from(
select * from sqltest pivot (sum(cum) for quarter in (1,2,3,4)));

在这里插入图片描述

注意:这里的1,2,3,4已经成为一个列名称,需要加上" ",不过这里相当于一个视图,而不是物理表,物理表是不能用纯数字做列名的。

--使用case when 也可以实现列转行,这个方法也比较常用
select 
id,
name,
sum(case when quarter = 1 then cum else 0 end ) as 第一季度,
sum(case when quarter = 2 then cum else 0 end ) as 第二季度,
sum(case when quarter = 3 then cum else 0 end ) as 第三季度,
sum(case when quarter = 4 then cum else 0 end ) as 第四季度
from sqltest
group by id,name

2,行转列

这里使用前面列转行的结果举例

---使用unpivot
select * from 
(select 
id,
name,
sum(case when quarter = 1 then cum else 0 end ) as 第一季度,
sum(case when quarter = 2 then cum else 0 end ) as 第二季度,
sum(case when quarter = 3 then cum else 0 end ) as 第三季度,
sum(case when quarter = 4 then cum else 0 end ) as 第四季度
from sqltest
group by id,name
)
unpivot (xiaoshou for jidu in (第一季度, 第二季度, 第三季度, 第四季度) )

在这里插入图片描述

四,lag,lead函数的使用

可以用于取今天和昨天的某字段差值

这里用xiaoliang这个表,里面记录了两个员工几天的销售量,

在这里插入图片描述

现要对销量按天为周期做个环比

SELECT 
emp,
riqi,
xiaoliang AS T_xiaoliang,
LAG(xiaoliang,1,0) OVER (PARTITION BY emp ORDER BY riqi ) Y_xiaoliang ,
(xiaoliang - LAG(xiaoliang,1,0) OVER (PARTITION BY emp ORDER BY riqi ) ) chazhi
FROM xiaoliang

在这里插入图片描述

五,三个排名分析函数

--1.row_number:返回连续的排序,无论值是否相等
SELECT sno,km,score,row_number() OVER (ORDER BY score DESC )FROM chengji;
SELECT sno,km,score,row_number() OVER (PARTITION BY km order by score DESC) FROM chengji;
--2.rank:具有相等值得行排序相同,序数值随后跳跃
SELECT sno,km,score,RANK() OVER (ORDER BY score DESC )FROM chengji;
SELECT sno,km,score,RANK() OVER (PARTITION BY km order by score DESC) FROM chengji;
--3.dense_rank:具有相等值得行排序相同,序号是连续的
SELECT sno,km,score,dense_rank() OVER (ORDER BY score DESC )FROM chengji;
SELECT sno,km,score,dense_rank() OVER (PARTITION BY km order by score DESC) FROM chengji;

六,ORACLE Top N,Last N

-- top n
select * from 
(SELECT emp,riqi,xiaoliang,row_number() OVER (ORDER BY xiaoliang DESC ) rn FROM xiaoliang)
where rn <=5;
select * from 
(SELECT emp,riqi,xiaoliang,row_number() OVER (PARTITION BY emp ORDER BY xiaoliang DESC) rn FROM xiaoliang)
where rn <=3; 
--Last N
select * from 
(SELECT emp,riqi,xiaoliang,row_number() OVER (ORDER BY xiaoliang) rn FROM xiaoliang)
where rn <=5;

七,oracle 删除重复数据,重复字段根据group by

DELETE from 
表 
WHERE id IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) 
AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

八,判断是否纯数字

如果字段为纯数字,则返回值为空,如字段含其他字符,则返回非数字的其他字符。

select
'123213' as chars,
decode(LTRIM('123213','0123456789'),null,'纯数字','包含其他字符') result
from dual
union all
select
'11ewewq233' as chars,
decode(LTRIM('11ewewq233','0123456789'),null,'纯数字','包含其他字符') result
from dual
union all
select
'1---?' as chars,
decode(LTRIM('1---?','0123456789'),null,'纯数字','包含其他字符') result
from dual

九,表复制

--只复制表结构,不复制数据
create table new_tablename as select * from old_tablename where 1=2
--只复制表结构和数据
create table new_tablename as select * from old_tablename 
--两种方式都不复制其他信息,比如主键,索引,注释,触发器

十,判断字段是否包含中文汉字,或者中文标点符号

lengthb(string)计算string所占的字节长度:返回字符串的长度,单位是字节

length(string)计算string所占的字符长度:返回字符串的长度,单位是字符

对于单字节字符,LENGTHB和LENGTH是一样的.

可以用length(‘string’)=lengthb(‘string’)判断字符串是否含有中文。

如果有中文,则不等

如果没有中文,则相等

select 'oracle表' as chars,
lengthb('oracle表'),
length('oracle表')
from dual
union all
select '数据库' as chars,
lengthb('数据库'),
length('数据库')
from dual
union all
select 'oracle' as chars,
lengthb('oracle'),
length('oracle')
from dual;

十一,时间相关的操作sql

select extract(year from sysdate) from dual;  --获取日期的年份
select extract(month from sysdate) from dual; --获取日期的月份
select extract(day from sysdate) from dual; --获取日期的天
select trunc(sysdate,'yyyy') from   dual ;--返回当年第一天
select trunc(sysdate,'mm') from   dual ; --返回当月第一天
select trunc(sysdate,'dd') from   dual ;--返回当前年月日
select to_char(last_day(sysdate),'DD') from dual;  --获取某月天数
select to_char(sysdate,'day') from dual;  --获取日期在星期几
select to_char(sysdate,'q') from dual;--获取日期所在季度
select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080228', 'yyyymmdd')) as months from dual; --两个日期之间的月份
select sysdate+1 from dual; --原来时间加1天
select sysdate+1/24 from dual; --原来时间加1小时
select sysdate+1/24/60  from dual; --原来时间加1分钟
select sysdate+1/24/2  from dual; --原来时间加30分钟

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!