【Oracle】常用、实用的SQL脚本（持续更新）_云计算

发布时间:2024-07-11 19:56

阅读量: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分钟