1.用户

-- 创建用户设置密码
create user ods identified by 123456;	-- 相当于创建ods数据库
-- 增加连接权限，和资源权限
grant connect,resource to ods
-- 解锁scott用户
alter user scott account unlock ;
-- 修改密码
alter user scott identified by 123456;

-- 复制表结构 以及 表数据
create table emp1 as select * from emp
-- 复制表结构 并且 不复制表数据
create table emp1 as select * from emp 1=2

-- 删除用户		【注意：必须是管理员权限，比如system】
drop user ods1;
-- 报错提示必须指定cascade删除   解决方法如下👇
drop user ods cascade	

数据库语言

DDL 数据库定义语言

-- 用于定义和管理SQL数据库中的所有对象的语言,对数据库中的某些对象(database,table)进行管理。
-- 包括的关键字有：create、alter、drop、truncate、comment、grant、revoke
-- DDL操作是隐性提交的，不能rollback

-- alter使用
-- 增加字段
alter table emp add(tname varchar(20))
-- 删除字段 （慎用！）没有回滚！
alter table emp drop (deptno)
-- 修改	（改大字符集可以，改小需要注意表中数据，有数据不能修改类型）
alter table emp modify (tname varchar(40))

DML数据库操作语言

-- 用户数据库的基本操作   指：基本的“增删改查”操作
-- 且DML操作是可以手动控制事务的开启、提交和回滚的
   
-- 手工（依靠手工）
for update （pl/sql小锁 解锁可以编辑修改）
例：select * from emp for update
提交commit	回滚rollback
-- 自动（依靠代码）
不基于原表：增加insert	删除delete	修改update
基于原表： 并入merge

-- 复制stu表给stu01（内容以及结构）
create table stu01 as select * from stu
-- 多条插入（必须保证stu的表结构 顺序和stu01一样）
insert into stu01 select * from stu

2.常用函数

字符串处理

拼接 concat()

-- 语法：concat()  【或者是双竖线 || 】
-- 【注意】concat()函数只能用于字符串的拼接
select 雇员ID,concat(姓氏,名字) 姓名 from 雇员        

select 雇员ID,姓氏||名字 姓名 from 雇员

截取 substr()

-- 语法：SUBSTR(string，start， [length])  只能用于字符串截取
-- string：表示源字符串，即要截取的字符串
-- start：开始位置，从第1位置查找，如果start是负数，则从字符串末尾开始
-- length：可选项，表示截取字符串长度

substr(str,-1,1)	-- 返回值：r

替换 replace()

-- 需求：202206   ——>   2022年6月
-- 思路：截取日期前4位拼接年；并且截取第5位（这里第5位如果0就为空）；第6位拼接月
substr(W_DAY_WID,1,4)||'年'||replace(substr(W_DAY_WID,5,1),'0','')||substr(W_DAY_WID,6,1)||'月'

字符串查找 instr()

-- 默认第一次出现“l”的位置
select instr('helloworld','l') from dual; 	-- 返回结果：3 	

-- 查询客户表中客户ID第二位时A的客户信息
select * from 客户 where INSTR(客户id, 'A')=2				 

长度 length()

-- 找出客户ID长度为5的订单
select * from 订单 where LENGTH(订单.客户ID) = 5

转文本 to_char()

-- 语法：to_char()		日期转换为文本
-- select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
TO_CHAR(订购日期,'WW') 		-- 第几周
TO_CHAR(订购日期,'D')		-- 周几 
TO_CHAR(订单.订购日期,'q')   -- 季度

-- 查询订购日期时当年第22周或者是周二的订单，结果输出订单ID，订购日期，第几周，周几
select 订单ID,订购日期,TO_CHAR(订购日期,'WW') 第几周,TO_CHAR(订购日期,'D') 周几 from 订单
where TO_CHAR(订购日期,'WW')=22 and TO_CHAR(订购日期,'D')=2

转数值 to_number()

-- to_number()  将字符串转换为数值型的格式

去空格 TRIM()

TRIM() 去空格，这里只能去前后空格

首字母大写 INITCAP()

INITCAP() 首字母转大写

例：select INITCAP('true') from 订单			结果从 true 转变为  True

-- 将是订单表中否已付里面为true改为首字母大写，结果输出订单ID，是否已付
select 订单ID,
case when 是否已付='true' then INITCAP('true') ELSE 'false' end 是否已付 from 订单

填充函数 LPAD() RPAD()

LPAD() 左边填充		 RPAD() 右边填充
-- 语法：lpad(str,len,padstr)
str:要处理的对象
len:处理完后的str长度为len
padstr:如果str的长度小于len指定的值，那么长度差由padstr在左边填充;如果str的长度大于len指定的值，则截取str到len指定的长度

题目

-- 语法：select RPAD(SUBSTR(sname,1,1), LENGTH(sname)+1, '*') from STUDENT

-- 题目：查询所有老师的信息，姓名去掉空格，并增加一列显示x老师，x是姓名的第一个字（如张三，张老师）
select tid,TRIM(tname), SUBSTR(tname,1,1)||'老师' tname2 from TEACHER
或者：【这里长度后+4，因为中文字母是两个占位符，我们’老师‘是两个字，所以加上4】
select tid,TRIM(tname), RPAD(SUBSTR(tname,1,1), LENGTH(tname)+4, '老师') tname2 from TEACHER

-- 输出学生的姓名和性别。将所有同学的名字隐藏只保留姓氏，而名字用*号代替，名字长度超过3位数的姓氏认为是一个。
-- 性别为男的，设置为小哥哥，性别为女的设置为小姐姐。(例如张三，张*韩梅梅，韩**)
select
RPAD(SUBSTR(sname,1,1), LENGTH(sname)+1,'*') sname,		-- 中文是两个占位符，在截取姓氏的基础长度上+1，补上姓的空缺
DECODE(ssex,'男','小哥哥','女','小姐姐') ssex 
from student

-- 筛选出区号为三位的客户，并把电话的格式转换成'***-72035188'
select '***'||SUBSTR(REPLACE(REPLACE(电话,'(',''),') ','-'), 4) from 客户
where instr(电话,')') = 5

日期处理

to_char(),sysdate，，trunc(),round(sysdate,'mm')精度,months_betweeen(日期1，日期2)，add_months(日期，2），last_day(),
extract (year | month | day | hour | minute | second | 某一时区 }
from { date类型值 | interval类型值} )
extract()只能从一个date类型中截取年月日

1.日期函数加或者减去一个数字仍然为日期
2.两个日期格式的数据相减得到的结果为天数

截取 extract()

-- extract()	从一个date类型中截取年月日
-- extract({ year | month | day | hour | minute |second | 某一时区 })
Select Extract(year from sysdate) from dual

-- 查询入职时超过30岁的员工的雇员信息
select * from 雇员 
where TO_CHAR(雇用日期,'yyyy') - TO_CHAR(出生日期,'yyyy') > 30
或者：
Select * from 雇员
where Extract(year from 雇用日期)  - Extract(year from 出生日期) > 30

-- 查询最近2个月过生日的学生信息（比如张三7月份过生日，那么5、6、7、8、9都包括在最近两个月内）
select * from STUDENT
WHERE EXTRACT(month from sage) BETWEEN EXTRACT(month from SYSDATE)-2 and EXTRACT(month from SYSDATE)+2

计算 add_months()

add_months(sysdate,12)    -- 当前日期加12月
add_months(sysdate,-12)   -- 当前日期减12月

select add_months(sysdate,-120) from dual   当前日期减10年         12月*10=120月

转日期 to_date()

-- 使用to_date()转日期函数结果集都是“年月日 时分秒”的格式
to_date( 2008 || '1231' , 'yyyymmdd' )    -- 返回值：2008-12-31 00:00:00

数值处理

绝对值 abs()

四舍五入 round()

round(库存量/单价,2)		-- 保留两位小数

取余 mod()

-- 语法：取余mod()		两数相除得余数 
-- 用于判断是否是单双数	【求偶数】MOD(供应商id,2)=0	    【求奇数】MOD(供应商id,2)=1			

-- 对产品表里面的供应商id将1变成2，2变成1，3变成4，4变成3依此类推跟换所有
select 产品id,产品名称,供应商id,
CASE WHEN MOD(供应商id,2)=0 THEN 供应商id-1 ELSE 供应商id+1 END 转换后供应商id from 产品

-- 求订单id最后一位不是单数的订单，结果输出订单id，订购日期
select 订单id,订购日期 from 订单
where  mod(substr(订单id,-1,1),2)=0

-- 查询所有订单中月份不是单数的订单的订单ID，月份
select TO_CHAR(订单.订购日期,'MM') from 订单
where MOD(TO_CHAR(订单.订购日期,'MM'), 2) = 0

取整 trunc()

trunc(订单.运货费+nvl(订单.应付金额,0))

运算符 + - * /

+	-	* 	/

-- 注意：null和任何数据做运算得到的结果都是null
想要不为null，使用nvl()函数	 例：select 应付金额,nvl(应付金额,0)+10 from 订单
-- 数值格式的数据
-- 日期格式的数据：日期格式的数据只能做加减运算，两个日期格式得字段相减得到两个日期相差的天数

3.聚合函数

与单行函数不同的是，聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用

count()	求返回的条数
sum() 
avg() 
max() 
min() 

分组

-- 按年度统计销售额
select TO_CHAR(订单.订购日期,'yyyy') 年份,sum(单价*数量*(1-折扣)) 销售额 from 订单
INNER JOIN 订单明细 on 订单.订单id = 订单明细.订单id
group by TO_CHAR(订单.订购日期,'yyyy')

-- 按每年各季度统计订单销售量
select TO_CHAR(订单.订购日期,'yyyy') 年份,TO_CHAR(订单.订购日期,'q') 季度,sum(数量) from 订单
INNER JOIN 订单明细 on 订单.订单id = 订单明细.订单id
GROUP BY TO_CHAR(订单.订购日期,'yyyy'),TO_CHAR(订单.订购日期,'q')

-- 查询哪些产品的年度销售额低于2000
select TO_CHAR(订单.订购日期,'yyyy') 年份,产品.产品id,sum(订单明细.单价*数量*(1-折扣)) 销售额 from 订单
INNER JOIN 订单明细 on 订单.订单id = 订单明细.订单ID
INNER JOIN 产品 on 订单明细.产品id = 产品.产品id
GROUP BY TO_CHAR(订单.订购日期,'yyyy'),产品.产品id
HAVING sum(订单明细.单价*数量*(1-折扣)) < 2000

-- 查询各月订单ID以6结尾的订单的订单量，并过滤低于订单量10的订单，结果输出月，订单量，销售额，结果按照销售额降序排序
select 订单.订单id,EXTRACT(month from 订购日期) 月份,count(*) 订单量,TRUNC(sum(订单明细.单价*订单明细.数量)) 销售额 from 订单
INNER JOIN 订单明细 on 订单.订单id = 订单明细.订单id
where 订单.订单id like '%6' 
GROUP BY 订单.订单id,EXTRACT(month from 订购日期)
HAVING count(*) < 10

4.偏移函数 lead() lag()

LEAD和LAG，这两个函数都是用来计算偏移量的分析函数，这两个函数的用法相同

-- lead基本用法
select e.ename,
       e.deptno,
       e.hiredate,
       lead(e.hiredate,1,null) over(partition by e.deptno order by e.hiredate) as next_hiredate
from emp e
where e.deptno=30;

题目

-- 考核通过优于考核未通过，总分高的优于总分低的，已知张三考了多次，查询他每次考核较上次考核是进步还是退步，结果输出考核日期、进退步情况
select 
tanme,
INSERTTIME,
case when 总分 > 上一次考核总分 or 考核结果 < 上一次考核结果 then '进步' else '退步' end as 进退步情况
from (
	with t as(
		select 
		TANME,FR_GRANDE,SQL_GRADE,TOMCAT_GRADE,KETTLE_GRADE,INSERTTIME,
		(FR_GRANDE+SQL_GRADE+TOMCAT_GRADE+KETTLE_GRADE) as 总分,
		case when (FR_GRANDE + SQL_GRADE + TOMCAT_GRADE + KETTLE_GRADE) >= 240 and FR_GRANDE>=60 and SQL_GRADE>=60 and TOMCAT_GRADE >= 60 then 1 
		else 2 end as 考核结果
		from SQL2
	)
	select 
	TANME,FR_GRANDE,SQL_GRADE,TOMCAT_GRADE,KETTLE_GRADE,INSERTTIME,总分,考核结果,
	lead(总分) over(partition by TANME order by INSERTTIME desc) 上一次考核总分,
	lead(考核结果) over(partition by TANME order by INSERTTIME desc) 上一次考核结果
	from t
)

5.递归

-- 语法
select level from dual connect by level <= 10;
select level from dual connect by 1=1;

with t as(
	select 'aa' chr from dual
	union ALL
	select 'bb' chr from dual
)
select level,chr,LPAD(' ', (level-1)*5, '-')||chr other from t connect by level <= 3


select 
TO_DATE(2008 || '0101' , 'yyyymmdd') + ROWNUM - 1 rq,
TO_CHAR(TO_DATE(2008 || '0101' , 'yyyymmdd') + ROWNUM - 1,'day') day
from (
	select rownum from dual 
	connect by rownum <= TO_DATE(2008 || '1231' , 'yyyymmdd') - TO_DATE(2008 || '0101' , 'yyyymmdd')+1
)

题目

create table menu(
	name VARCHAR2(20),
	id int,
	parent_id int
);
select * from menu;
insert into menu values('理科',1,0);
insert into menu values('文科',2,0);
insert into menu values('建筑',3,1);
insert into menu values('桥梁',4,1);
insert into menu values('通信',5,1);
insert into menu values('古代建筑',6,3);
insert into menu values('现代建筑',7,3);
insert into menu values('古代寺庙',8,6);
insert into menu values('古代民房',9,6);
insert into menu values('历史',10,2);
insert into menu values('社会学',11,2);
insert into menu values('伦理学',12,11);
insert into menu values('中国历史',13,10);
insert into menu values('世界历史',14,10);
insert into menu values('中国古代史',15,13);

/*
（1）理科下面的第三层以下菜单，要求显示为：
古代建筑
             古代寺庙
             古代民房
现代建筑
*/
with t as(
	select sys_connect_by_path(name,' ') as 层级,LEVEL,LPAD(name, (LEVEL)*8, ' ') from menu 
	start with name = '古代建筑'
	connect by prior id = parent_id
	union all 
	select sys_connect_by_path(name,' ') as 层级,LEVEL,LPAD(name, (LEVEL)*8, ' ') from menu 
	start with name = '现代建筑'
	connect by prior id = parent_id
)
select * from t


/*
（2）按照层级结构显示所有的关系，层次关系用空格多少表达 结果：
 理科
       建筑
            古代建筑
                      古代寺庙
                      古代民房
            现代建筑
       桥梁
       通信
 文科
 */
with t as(
	select name,LEVEL,LPAD(name, (LEVEL)*6, ' ') from menu 
	start with name = '理科'
	connect by prior id = parent_id
	union all 
	select name,LEVEL,LPAD(name, (LEVEL)*6, ' ') from menu 
	start with name = '文科'
	connect by prior id = parent_id
)
select * from t

-- 可以用case when 进行level判断层级 并且添加对应的空格个数
-- 递归拼接函数  sys_connect_by_path(部门名称,'->')

6.查询

表连接

-- innner join 
-- left join
-- right join
-- union
-- union all 

-- union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序; 
-- union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;
SELECT * FROM STUDENT WHERE cno = 1001 
UNION 
SELECT * FROM STUDENT WHERE cno = 1001		-- 返回6条数据

SELECT * FROM STUDENT WHERE cno = 1001 
UNION all
SELECT * FROM STUDENT WHERE cno = 1001 		-- 返回12条数据


-- 分别各写一个查询，得到订单中折扣为15%，20%的所有订单，并将两个查询再组成一个
select * from 订单
INNER JOIN 订单明细 on 订单.订单id = 订单明细.订单id
where 订单明细.折扣 = 0.15
UNION ALL
select * from 订单
INNER JOIN 订单明细 on 订单.订单id = 订单明细.订单id
where 订单明细.折扣 = 0.20

列转行

行转列 pivot()

列转行unpivot()

case when

-- 对产品表里面的供应商id将1变成2，2变成1，3变成4，4变成3依此类推跟换所有
select 产品id,产品名称,供应商id,
CASE WHEN MOD(供应商id,2)=0 THEN 供应商id-1 ELSE 供应商id+1 END 转换后供应商id
from 产品

-- 题目：列转行
怎么把这样一个表
year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1   m2   m3   m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 
-- 答案：
select
YEAR,
max(CASE WHEN AMOUNT=1.1 THEN 1.1
				 WHEN AMOUNT=2.1 THEN 2.1 END) m1,
max(CASE WHEN AMOUNT=1.2 THEN 1.2
				 WHEN AMOUNT=2.2 THEN 2.2 END) m2,
max(CASE WHEN AMOUNT=1.3 THEN 1.3
			   WHEN AMOUNT=2.3 THEN 2.3 END) m3,
max(CASE WHEN AMOUNT=1.4 THEN 1.4
			   WHEN AMOUNT=2.4 THEN 2.4 END) m4
from BRITH
GROUP BY YEAR

判断处理

between and

-- 查询最近2个月过生日的学生信息（比如张三7月份过生日，那么5、6、7、8、9都包括在最近两个月内）
select * from STUDENT
WHERE EXTRACT(month from sage) BETWEEN EXTRACT(month from SYSDATE)-2 and EXTRACT(month from SYSDATE)+2

decode()

(ssex,'男', '小哥哥','女', '小姐姐')		括号里面（字段，值1，返回值1，值2，返回值2） 且decode里面可以嵌套decode

7.开窗函数

-- 开窗函数:   函数() over(partition by 分组列 order by 排序列 desc)

1. 聚合函数
select sid,cid,score,sum(score)over() from score
select sid,cid,score,sum(score)over(partition by cid ) from score
select sid,cid,score,sum(score)over(partition by cid ORDER BY sid) from score 滚动叠加

2.
rank()  dense_rank()    row_number()
select sid,cid,score,rank()over(partition by cid ORDER BY score) from score 
select sid,cid,score,dense_rank()over(partition by cid ORDER BY score) from score 
select sid,cid,score, row_number()over(partition by cid ORDER BY score) from score 

3.
偏移函数
lead ()   lag()
select sid,cid,score, lag(score,1,0)over(partition by cid ORDER BY score),lead(score,1,0)over(partition by cid ORDER BY score)
 from score 
 
=========================================================窗口函数============================================
## 序号函数		
ROW_NUMBER()	【排序】“分组的时候price字段相同的数据按照行号排列   序号为：1 2 3 4 5 ”
RANK()		 	【并列跳级】“分组的时候price字段相同的数据序号也相同且结果多少条不变 序号为：1 2 2 4 5 ”
DENSE_RANK()	【并列不跳级】“分组的时候price字段相同的数据序号也相同，依次排列	序号为：1 2 2 3 4 ”
  
## 分布函数
PERCENT_RANK()	【等级值百分比函数，计算方式：(rank - 1) / (rows - 1) 】
				【其中：rank的值为使用rank()函数产生的序号，rows的值为当前窗口的总记录数】
CUME_DIST()		用于查询小于或等于某个值的比例

## 前后函数
LAG(expr,n)			返回当前行的前n行和expr的值
LEAD(expr,n)		返回当前行的后n行和expr的值

## 首尾函数
FIRST_VALUE(expr)	返回第一个expr的值
LAST_VALUE(expr)	返回最后一个expr的值

## 其他函数
NTH_VALUE(expr,N)     返回第n个的expr的值
NTILE(N)	          将分区中的有序数据分为n个桶，记录有桶编号
===============================================开多个窗口====================================================
-- 【语法】rows between ... preceding and ... following
-- current row  	当前行
-- preceding		之前
-- following		之后

-- 取当前行 和 当前行的前1行和后1行  一共3条数据为窗口
select SUM(status) over(partition by sid order by seat rows between 1 preceding and 1 following) from t
-- 取当前行 和 当前行的前2行				 一共3条数据为窗口
select SUM(status) over(partition by sid order by seat rows between 2 preceding and current row) from t
-- 取当前行 和 当前行的后2行         一共3条数据为窗口

题目1

-- 查询每门功成绩最好的前两名
select * from (
	select sc.*,RANK() over(partition by cid ORDER BY SCORE DESC) 排名 from score sc
)WHERE 排名 <= 2

题目2

-- 查询每个班级总分最好的前两名
select * from (
	select cid,sid,学生总分,rank() over(partition by cid ORDER BY 学生总分)  排名  from (
		select sc.cid,sc.sid,SUM(sc.score) 学生总分 from class c
		left join student stu on c.cno = stu.cno 
		left join score sc on stu.sid = sc.sid
		GROUP BY sc.cid,sc.sid
		ORDER BY sc.cid
	)
)where rownum <= 2

题目3

-- 查询每类产品中销售额（单价*库存量）最高的产品，如果有多个取单价最低的那个，结果输出，类别ID,产品ID，排名，销售额，单价
select * from (
	select 
	类别id,
	产品ID, 
	rank() over(partition by 类别id order by 单价*库存量 desc,单价 asc) 排名,
	(单价*库存量) 销售额,
	单价 
	from 产品 
)
where 排名 = 1

题目4

-- 查询学生的总成绩，并进行排名，总分相同时名次相同，且跳过原有的名次（1,1,3,4）
select id,sid,总成绩,rank() over(partition by id order by 总成绩) 排名 from (
	select 'a' as id,sid,sum(score) 总成绩 from score
	GROUP BY sid
)

-- 查询学生的总成绩，并进行排名，总分相同时名次相同，且不跳过原有的名（1,1,2,3）
select id,sid,总成绩,dense_rank()over(partition by id order by 总成绩) 排名 from (
	select 'a' as id,sid,sum(score) 总成绩 from score
	GROUP BY sid
)

8.子查询

-- 1.
select * from (select * from score where cid=01)

-- 2. 
select * from score
where sid in  (select sid from score where cid=01)

-- 3.
select * from score
where score > (select avg(score) from score )

-- 4.
select * from score t1
where t1.cid=01 and sid in (select sid from score where cid=02)

-- 5.
select * from score t1
where t1.cid=01 and exists (select 1 from score t2 where t1.sid=t2.sid and t2.cid=02) 

select * from score t1
inner join score t2 on t1.sid=t2.sid and t2.cid=02

-- 找出订单销售额前五的订单是经由哪家运货商运送的。
select * from (
	select 
	订单.订单id,
	运货商.公司名称,
	TRUNC(sum(订单明细.单价*订单明细.数量*(1-折扣))) 销售额 
	from 订单
	INNER JOIN 订单明细 on 订单.订单id = 订单明细.订单id
	INNER JOIN 运货商 on 订单.运货商id = 运货商.运货商id
	GROUP BY 订单.订单id,运货商.公司名称
	ORDER BY 销售额 desc
)ROWNUM <= 5

-- 查询分数大于整体平均分的学生信息
select * from SCORE
where score > (select avg(score) from score)

-- 查询学过的科目数跟学号为02一模一样的学生
select * from (
	select sid,COUNT(cid) total from SCORE
	where sid != 02
	GROUP BY sid
) where total = (
	select COUNT(cid) total from score
	where sid = 02
)

-- 查询既学过01课程又学过02 课程的学号和姓名
select c.sid,c.SNAME from score a
INNER JOIN SCORE b on a.sid = b.sid and a.cid = 01 and b.cid = 02
INNER JOIN STUDENT c on a.sid = c.sid

9.同比环比 ⭐

当月销量
去年当月销量
同期增长值 = 当月销量 - 去年当月销量
同比 = 同期增长值 / 去年当月销量
环比 = (当月销量 - 前一月销量) / 前一月销量

【注意】 今年比去年 今年 left join 去年

遇到同比环比，就是今年比去年，用left join，今年是主表，去年是次表

今年减一年才可以和去年的日期匹配相等

同理，去年加一年才可以和今年的日期匹配相等

不要纠结于a表和b表哪一个代表今年或者去年，纠结这个就是错！！！

-- 今年比去年   今年减1年   才可以和去年的日期匹配相等
select * from HR.SQL1 a
LEFT JOIN HR.SQL1 b on ADD_MONTHS(a.yearmonth,-12) = b.yearmonth
-- 或者   去年加1年	才可以和今年的日期匹配相等
select * from HR.SQL1 a
LEFT JOIN HR.SQL1 b on a.yearmonth = ADD_MONTHS(b.yearmonth,12)

题目说明

【今年 比 去年】 这里重点是今年1月 去年就是12月

-- 如果是年月两个分开的字段
-- 则需要:
from t a 
LEFT JOIN t b 
on a.年 = b.年 and a.月-1 = b.月 
or a.月 = 1 and b.月 = 12 and a.年-1 = b.年

-- 或者
select * from SQL5 a
LEFT JOIN SQL5 b on a.TYEAR = b.TYEAR and a.TMONTH-1 = b.TMONTH
or a.TMONTH = 01 and b.TMONTH = 12 and a.TYEAR = b.TYEAR+1

求同比 环比时，除数不能为0 处理

-- 同比
ROUND((a.SALEAMOUNT - NVL(b.SALEAMOUNT,0)) / case when nvl(b.saleamount,0)!=0 then b.saleamount end,2) as 同比

-- 环比
ROUND((a.SALEAMOUNT - nvl(b.saleamount,0)) / case when nvl(b.saleamount,0)!=0 then b.saleamount end,2) as 环比

题目1

-- 19年各月销量默认为0，同期增长值=当月销量-去年当月销量，同比=同期增长值/去年当月销量,查询结果输出年月、当月销量、同期增长值、同比，结果按年月顺序排序
with t as (
 select SUBSTR(TO_CHAR(a.YEARMONTH,'yyyy-mm'),1,4) as 年,SUBSTR(TO_CHAR(a.YEARMONTH,'yyyy-mm'),6,2) as 月,a.SALEAMOUNT 
 from SQL1 a 
)
SELECT 
a.年 || '-' ||a.月 as 年月,
a.SALEAMOUNT as 当月销量,
a.SALEAMOUNT - NVL(b.SALEAMOUNT, 0) as 同期增长值,
ROUND((a.SALEAMOUNT - NVL(b.SALEAMOUNT, 0)) / (case when nvl(b.saleamount,0)!=0 then b.saleamount end), 2) as 同比
FROM t a  
LEFT JOIN t b 
on to_number(a.年) -1 = to_number(b.年) and to_number(a.月)=to_number(b.月)
ORDER BY 年月

题目2

-- 19年各月销量默认为0，环比=（当月销量-前一月销量）/前一月销量，查询结果输出年月、当月销量、环比，结果按年月顺序排序
with t as (
 select SUBSTR(TO_CHAR(a.YEARMONTH,'yyyy-mm'),1,4) as 年,SUBSTR(TO_CHAR(a.YEARMONTH,'yyyy-mm'),6,2) as 月,a.SALEAMOUNT 
 from SQL1 a 
)
select 
a.年 || '-' ||a.月 as 年月,
a.SALEAMOUNT as 当月销量,
b.SALEAMOUNT as 前一月销量,
ROUND((a.SALEAMOUNT - nvl(b.saleamount,0)) / case when nvl(b.saleamount,0)!=0 then b.saleamount end,2) as 环比
from t a 
LEFT JOIN t b 
on a.年 = b.年 and a.月-1 = b.月 
or (a.月 = 1 and b.月=12 and a.年-1 = b.年)