【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’。）
还有其他方法：https://blog.csdn.net/u012414590/article/details/71435440
三，行与列相互转换
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分钟
325
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
