《精通oracle database 12c编程》

最新推荐文章于 2024-04-18 05:31:41 发布

NellieXU

最新推荐文章于 2024-04-18 05:31:41 发布

阅读量316

点赞数

文章标签： oracle Powered by 金山文档

本文链接：https://blog.csdn.net/m0_73493836/article/details/128640418

版权

《精通oracle database 12c编程》

第一章简介

删除和创建用户store

Drop USER store CASCADE；--cascade用法

Create user store IDENTIFIED BY store_password;

Grant connect，resource TO store；--允许连接并创建数据库，权限由特权用户如system用户授予

分配表空间存储

Alter user store QUOTA 10M ON users；--为store用户在users表空间分配10MB表空间

其中的表空间名users可使用下一语句查询：

SELECT property_value

FROM database_properties

WHERE property_name = ‘DEFAULT_PERMANENT_TABLESPACE’;--返回表空间名

建表

ID 用主键约束以及整数类型

Create table customers (

customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,

NAME VARCHAR2(10) NOT NULL)

--INTEGER 整数类型

--NUMBER(a,b) 浮点类型，小数点左边最多存储（a-b）位，小数点右边最多存储b位

第二章检索信息

别名

Select price*2 “double price”--用双引号可以给别名中间加空格

From price；

连接符输出合并字段

Select first_name ||‘ ’||last_name from customers；--将原表的两字段合并为一列输出查询

比较值

select * from emp where ID>ANY(2,3,4)--相当于min

select * from emp where ID>some(2,3,4)--相当于any，一般用any

select * from emp where ID>ALL(2,3,4)--相当于max

NOT 操作符

NOT LIKE;NOT IN ;NOT BETWEEN;IS NOT NULL;仅限于sqlplus：IS NOT NAN;IS NOT INFINITE;

模糊查询

Select name

From emp

Where name like ‘%/%%’ escape ‘/’;--escape 用来区分要搜索的字符与通配符，第一和第三个%是通配符，第二个%是要搜索的实际字符

Order by 1--按照第一列字段进行排序

表连接

左外连接：（+）放在从表后

使用限制：where 关联条件不能与OR操作符同用

Using：SELECT * FROM EMP INNER JOIN DEPT

USING(ID);

用using 关联条件可以取代where或者ON

Using的使用限制：查询必须是等值连接；关联字段必须同名

笛卡尔乘积：交叉连接CROSS JOIN

SELECT * FROM EMP

CROSS JOIN DEPT;

第四章使用单行函数

NANVL函数--返回数字

Select nanvl（2,0）from dual；--2

如果2是数字，则返回2；否则返回0

cast函数

属于转换函数

Select cast（‘x’as TYPE）FROM DUAL;--将x转换为 type类型，type可以是任一指定类型

正则表达式函数

REGEXP_LIKE

SELECT * FROM EMP WHERE REGEXP_LIKE(ID,’^[4-9]$’);--查询emp中id字段的值中在4-9范围内的值

REGEXP_LIKE(name,’^[a-z]{5}\,’)--表示以[a-z]小写字母开头且有5个字母并以，结尾的字符串如birth，

{5}表示前面的匹配模式重复循环5次

\匹配特殊字符

INITCAP

语法：INITCAP（string）

功能：返回字符单词首字母大写，其余小写，单词用空格和非字母字符分隔。

例如：DECODE-Decode

第五章时间值函数

ADD_MONTHS(x,y)--月份的加减

LAST_DAY(x)--包含x的当月最后一天

MONTHS_BETWEEN(x,y)--计算月份差

NEXT_DAY(x,day)

ROUND()

SYSDATE

TRUNC()

第六章子查询

1.单行子查询（标量子查询）

位置：where、having、from之后+子查询

在from后+子查询又叫做内联视图

注意：在子查询中不能用order by 子句，order by 必须放在外部查询

多行子查询

用where。。。In、<any、>all

3.多列子查询

Select * from emp where（ID，name）in(select id,name from dept);--返回多列

检索每种类型中价格最低的产品：

Select id,name,price

From products

Where (id,price) in

(select id,min(price)

From products

Group by id)

Order by id;

关联子查询

又叫自关联

Select ID ,PRICE from emp T1

Where PRICE>

（select AVG(PRICE)

FROM EMP T2

WHERE T1.ID=T2.ID）

ORDER BY ID;

可以使用exists和not exists 对关联子查询进行优化

检测没有订单的客户

Select customer_id,name

From customers c

Where not exists(

Select 1

From orders p

Where p.id=c.customer_id)

Order by customer_id;

5.嵌套子查询：执行查询顺序是由内到外

6.with as 使用子查询因子化

可以把自查询放在with子句中，并在with子句外面引用自查询

第七章高级查询

使用集合操作符

操作符	说明
UNION ALL	返回各个查询检索出的所有行，包括重复的行
UNION	返回各个查询检索出的所有行，不包括重复的行
INTERSECT	返回两个查询检索出的共有行
MINUS	返回从第一个查询检索出的行中减去第二个查询检索出的行之后剩余的行

注意：组合使用时，默认顺序自上而下，但通常使用圆括号指示求值顺序

TRANSLATE（x,from_string,to_string）函数

将x按from_string到to_string的方式，单个字母分别替换，传递机密时适用

decode函数（旧版本，已不推荐使用），被case函数代替

Case函数

SELECT CASE 字段名

WHEN ‘字段值1’ THEN 结果1

Else 结果2

END

From 表；

层次化查询

家族树

根节点-父节点-子节点-叶节点兄弟节点

Select LEVEL--使用伪列显出节点的层次

,employee_id

,manager_id

,name

From emp

Start with employee_id=1

Connect by prior employee_id--父节点

=manager_id--子节点

Order by level;--从上往下遍历树

Select LEVEL--使用伪列显出节点的层次

,LPAD(‘’,2*LEVEL-1)||NAME AS employee

From emp

Start with employee_id=1

Connect by prior manager_id--子节点

= employee_id--父节点

Order by level;--从下往上遍历树

若要从查询中删除节点和其分支：

在connect by 子句之后、order by子句之前，加上

AND name != ’ x’

若使用where子句在from子句和start with子句之间，则只会过滤掉该节点，不会过滤该节点下包含的分支

但可以使用where子句加入其他条件，比如过滤掉工资少于5000的员工

难点：使用with as递归子查询因子化查询分层数据

使用ROLLUP子句和CUBE子句

6.1 ROLLUP子句：

SELECT ID,SUM(PRICE) FROM E

GROUP BY ROLLUP(ID)--显示价格的总计和按ID分组求和

OEDER BY ID;

任何分组函数都可以使用rollup函数来改写分组统计

6.2 CUBE子句：

SELECT ID,SUM(PRICE) FROM E

GROUP BY CUBE(ID)--显示价格的总计和按ID分组求和

OEDER BY ID;

6.3 使用grouping子句

，使得为空的字段值返回1，非空返回0

结合rollup子句和cube使用

SELECT GROUPING( ID),ID,SUM(PRICE) FROM E

GROUP BY ROLLUP(ID)--显示价格的总计和按ID分组求和

OEDER BY ID;

6.4 结合case子句使用grouping

SELECT CASE GROUPING( ID)

WHEN 1 THEN ‘SUM’

ELSE TO_CHAR(ID)

END AS ID_SUM

,SUM(PRICE)

FROM E

GROUP BY ROLLUP(ID)

OEDER BY ID;

6.5多列：

Select

CASE GROUPING(DIV_ID)

WHEN 1 THEN ‘ALL DIV’

ELSE DIV_ID

END AS DIV

,CASE GROUPING(JOB_ID)

WHEN 1 THEN ‘ALL JOBS’

ELSE JOB_ID

END AS JOB

,SUM(SALARY)

FROM EMP

GROUP BY ROLLUP(DIV_ID,JOB)

OEDER BY DIV_ID,JOB;

6.6 小计

Grouping sets子句：性能比cube好

Select div_id,job_id,sum(salary)

From employees2

Group by Grouping sets（div_id,job_id）

ORDER BY div_id,job_id;

Update用法

如：交换性别题

Column Name | Type |

+-------------+----------+

| id | int |

| name | varchar |

| sex | ENUM |

| salary | int |

+-------------+----------+

id 是这个表的主键。

sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。

本表包含公司雇员的信息。

请你编写一个SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用单个 update 语句，且不产生中间临时表。

--oracle 没有enum 枚举值类型，用varchar2代替，并加上check约束

注意，你必须仅使用一条update 语句，且不能使用 select 语句。

# 1.update salary set sex = (

case sex when 'm' then 'f' else 'm' end

);

# 2.update salary set sex = chr(ascii('m') + ascii('f') - ascii(sex));

来源：力扣（LeetCode）

链接：https://leetcode.cn/problems/swap-salary

7.7.1 CROSS APPLY

这是oracle 12c新出的函数

适用：查找有订单的客户

Select * from orders o

CROSS APPLY--返回从两条select语句合并的行（只返回匹配的结果）

(select * from customers c

Where o.id=c.customer_id)

Order by id;

7.7.2 OUTER APPLY

适用：查找所有客户和对应订单

Select * from orders o

OUTER APPLY--返回从两条select语句合并的行（无论是否匹配都返回的结果）

(select * from customers c

Where o.id=c.customer_id)

Order by id;

7.7.3 LATERAL

适用：返回内联视图

Select * from orders o

LATERAL--返回从两条select语句合并的行（只返回匹配的结果）

(select * from customers c

Where o.id=c.customer_id)

Order by id;

区别于CROSS APPLY：LATERAL提供一个子查询作为内联视图

第八章分析函数

8.1.1 评级函数

8.1.1.1排名函数

ROW_NUMBER()

1/2/3/4/5

DENSE_RANK()

1/2/2/3/3

RANK()

1/2/2/4/4

空值默认最大

也可以使用NULLS FIRST 和NULLS LAST 使得空值最先或者最末

Select ID，salary，rank（）over（order by salary desc NULLS FIRST）FROM SALARY;--空值第一

Select ID，salary，rank（）over（order by salary desc NULLS LAST）FROM SALARY;--空值最末

其他排名：cume_dist和percent_rank

percent_rank计算方法为：

(RANK - 1)/(N- 1)

N为总数

8.1.1.2切片：NTILE（）

8.1.2反百分位函数

Percentile_disc(x)

Percentile_cont(x)

8.1.3窗口函数

8.1.3.1 求和

SUM（）OVER(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

--UNBOUNDED PRECEDING 起点在结果集的起始行

--CURRENT ROW 处理结果集的当前行

8.1.3.2平均值

移动平均值

AVG()OVER(ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

--处理当前行与前三行的移动平均值，即一共四行的移动平均，不足4行的按可用的月份平均

--3 PRECEDING起点在结果集的当前行的往前数第四行

--CURRENT ROW 处理结果集的当前行

中心移动平均值

计算当前行以及前后各1行的中心移动平均值

AVG()OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

起点：当前行的前一行

终点：当前行的下一行

8.1.3.3 FIRST_VALUE和LAST_VALUE

分别返回前一行数值和后一行数值

FIRST_VALUE()OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

LAST_VALUE()OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

以下可以返回当月销售额/上月销售额，即同比增长率

sum(amount)/FIRST_VALUE(sum(amount)) OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

8.1.3.4 NTH_VALUE（n）函数

返回窗口中的第n行

SELECT SEX,SUM(SALARY),NTH_VALUE(SUM(SALARY),2)OVER (ORDER BY SEX ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)--返回第二行的值

也可以结合partition by和group by返回分组第n行的数据

8.1.4报表函数

8.1.4.1总计报表

使用聚合函数（别名：分组函数）搭配例如：sum、max、count、variance、stddev

sum（sum（amount））over（partition by month）--每月销售总额

sum（sum（amount））over（partition by prd_type_id）--每种产品类型的销售总额

8.1.4.2 RATIO_TO_REPORT

计算占比

Select month，prd_type_id，sum（amount）--每种类型每个月的销售额

RATIO_TO_REPORT（sum（amount））over（partition by month）--每种产品类型的销售额占当月销售额的比率

Group by month，prd_type_id

8.1.5 listagg 函数

其主要功能类似于 wmsys.wm_concat 函数，即将数据分组后，把指定列的数据再通过指定符号合并

Select

Listagg（name, ’ , ’）within GROUP (ORDER BY price,name) as “product list”,

Max(price) as “max expensive”

From products

Where product_id<=5;

8.1.6 LAG函数和LEAD 函数

可以获得位于当前行指定距离的行数据，与first_value 和last_value效果略有差别

LAG（sum（amount），1）over（order by month）--前一个月的销售额

Lead（sum（amount），1）over（order by month）--后一个月的销售额

8.1.7 FIRST 函数和LAST函数

获得排序分组中的第一个值和最后的值

Select

Min（month）Keep（dense_rank FIRST ORDER BY sum（amount）），--销售额最低的月份

Min（month）Keep（dense_rank LAST ORDER BY sum（amount））--销售额最高的月份

FROM ALL_SALES

GROUP BY MONTH

ORDER BY MONTH;

8.1.8 线性回归函数

(54条消息) oracle regr slope 例,Oracle 11g学习笔记--分析函数_Secretypo的博客-CSDN博客

REGR_AVGX(Y,X)

8.1.9 使用假想评级与分布函数

所谓假想，就是虚构一个值，计算它在该分组中的排名

范例：计算销售额 $500000 的假想排名和百分比排名

SELECT

RANK(500000)WITHIN GROUP(ORDER BY SUM(amount) DESC) AS rank,

PERCENT_RANK(500000)WITHIN GROUP(ORDER BY SUM(amount) DESC) AS percent_rank

FROM all_sales

WHERE year=2003

AND amount IS NOT NULL

GROUP BY prd_type_id

ORDER BY prd_type_id;

————————————————

原文链接：https://blog.csdn.net/u012456926/article/details/41415215

8.2 MODEL 子句

8.2.1用model预测未来月份的销售额--实例1

下面这个查询获取2003年内由员工#21完成的产品类型为#1和#2的销量，并根据2003年的销售数据预测出2004年1月、2月、3月的销量。

select prd_type_id,year,month,sales_amount

from all_sales

where prd_type_id between 1 and 2

and emp_id=21

model

partition by (prd_type_id)

dimension by (month,year)

measures (amount sales_amount)

(

Sales_amount[1,2004]=sales_amount[1,2003],

Sales_amount[2,2004]=sales_amount[2,2003] + sales_amount[3,2003],

Sales_amount[3,2004]=ROUND(sales_amount[3,2003]*1.25,2)

)

Order by prd_type_id,year,month;

————————————————

原文链接：https://blog.csdn.net/iteye_12255/article/details/81927296

8.2.2 预测2003年大陆地区的进货和收益--实例2

几点说明

model子句只是单纯的返回一个结果集，不会对源表产生任何影响。
B、如果想使用model自己产生的结果集可以使用insert ，update，或者merge into子句完善功能。
C、根据表结构和需求定义分区（partition by），维度（dimension by ），度量（measures），其中分区表名了规则适用的范围和作用域，维度用来唯一的定定位一个单元格，度量用来真正存储规则产生的结果（自己的理解）。
D、根据下述表结构定义上面提到的三个要素：分区：domains ，维度：name 和 year ，度量：inputs ，increaments 。

别名

获取度量字段别名，在measures（）中加上别名，用别名替换原表字段，就可以直接在select 度量值、rules （）中使用别名

8.2.3位置标记和符号标记：dimension by（）的括号内是定义数组的维度，在measures (amount sales_amount)后用到的Sales_amount[1,2004]就是属于位置标记的用法，位置标记也可以用符号标记来改写：Sales_amount[month=1,year=2004]，以此类推，都改写成这种定义维度的形式；

位置标记和符号标记两者之间区别在于：

a、处理维度中空值的方式不同。Sales_amount[null,2004]返回月份空值，年份2004的度量值；Sales_amount[month=null,year=2004]返回无效，因为null=null的返回值为false

b、符号标记不同于位置标记，如果不存在不会新增

意思是符号标记必须用原表中有的维度，而对于原表中没有的（month，year），不会返回值。

实例2：使用位置标记返回多度量

SELECT domains,

NAME,

YEAR,

inputs,

increaments

FROM modeL_test

WHERE domains = 'chchina'

MODEL RETURN UPDATED ROWS

PARTITION BY (domains)

DIMENSION BY (NAME, YEAR)

MEASURES (inputs, increaments)

RULES

(inputs ['apple', '2003'] = 2 * inputs['apple', '2000'],

increaments ['apple', '2003'] = 2 * increaments['apple', '2000'],

inputs ['banana', '2003'] = 2 * inputs['banana', '2001'],

increaments ['banana', '2003'] = 2 * increaments['banana', '2001'],

inputs ['orange', '2003'] = 2 * inputs['orange', '2002'],

increaments ['orange', '2003'] = 2 * increaments['orange', '2002']);

由此可以看出model子句支持多维度和多度量的实现，另在model后面加RETURN UPDATED ROWS ，目的在于只返回受影响的记录，表中的原始数据不再显示。

8.2.4返回特定范围内的维度

使用between和and

Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2)

B.使用in，<>

RULES (inputs [NAME IN ('apple','orange'), YEAR<'2003'] = 0)

8.2.5用ANY和IS ANY访问所有的数据单元

可以用ANY和IS ANY谓词访问数组中所有的数据单元。ANY和位置标记合用，IS ANY和符号标记合用。例如，下面这个表达式将2004年1月的销量设置为所有年份月份的销量之和取整：

Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2)

8.2.6使用cv函数和any运算符。

CV() 函数是一个非常强大的工具，它可以高效地进行规则创建。CV() 用于规则的右侧，以复制左侧指定的当前维度值。对于左侧规范引用多个单元格来说，它非常有用。

SELECT domains,NAME,YEAR,inputs ,increaments

FROM modeL_test

WHERE domains='chchina'

MODEL RETURN UPDATED ROWS

PARTITION BY (domains)

DIMENSION BY (NAME,YEAR)

MEASURES ( inputs,increaments)

RULES (

inputs['apple',YEAR BETWEEN 2000 AND 2004]=inputs['orange',cv(YEAR)]+600,

increaments['apple',YEAR BETWEEN 2000 AND 2004]=increaments['orange',cv(YEAR)]*2);

等价于：

SELECT domains,NAME,YEAR,inputs ,increaments

FROM modeL_test

WHERE domains='chchina'

MODEL RETURN UPDATED ROWS

PARTITION BY (domains)

DIMENSION BY (NAME,YEAR)

MEASURES ( inputs,increaments)

RULES (

inputs['apple',ANY ]=inputs['orange',cv(YEAR)]+600,

increaments['apple',ANY ]=increaments['orange',cv(YEAR)]*2

);

等价于：

SELECT domains,NAME,YEAR,inputs ,increaments

FROM modeL_test

WHERE domains='chchina'

MODEL RETURN UPDATED ROWS

PARTITION BY (domains)

DIMENSION BY (NAME,YEAR)

MEASURES ( inputs,increaments)

RULES (

inputs['apple',year IS ANY ]=inputs['orange',cv(YEAR)]+600,

increaments['apple',YEAR IS ANY ]=increaments['orange',cv(YEAR)]*2

);

8.2.7 使用currentv()

该函数用于获的某个维度的当前值。

--表示将2004年第一个月的销量设置为2003年同月销量的1.25倍。注意此处用currentv()获取当前月份，其值为1；sales_amount [1, 2004] = round(sales_amount[currentv(), 2003] *1.25, 2)

8.2.8 使用FOR 子句循环

该表达式将2004年前三个月的销量设置为2003年相应月份销量的1.25倍。其中increment 1表示了变量每次循环+1

sales_amount[for month from 1 to 3 increment 1, 2004] = round (sales_amount[currentv(), 2003] * 1.25， 2)

递增用increment ，递减用decrement

8.2.8 处理空值和缺失值

8.2.8.1 IS PRESENT子句

当数据单元指定的单位在model子句执行之前存在，则is precent返回ture.

sales_amount[for month from 1 to 3 increment 1, 2004] =

case when sales_amount[currentv(), 2003] is present

then

round (sales_amount[currentv(), 2003] * 1.25, 2)

else

end

8.2.8.2 使用presentv

如果cell引用的记录在model子句执行之前就存在，那么presentv(cell, expr1, expr2)返回表达式expr1。如果这条记录不存在，则返回表达式expr2。

sales_amount[for month from 1 to 3 increment 1, 2004] = presentv(sales_amount[currentv(), 2003],

round(sales_amount[currentv(), 2003] * 1.25， 2), 0)

8.2.8.3 使用presentnnv

presentnnv(cell, expr1, expr2)如果cell引用的单元在model子句执行之前已经存在，并且该单元的值不为空，则返回表达式expr1。如果记录不存在，或单元值为空，则返回表达式expr2;

sales_amount[for month from 1 to 3 increment 1, 2004] = presentnnv(sales_amount[currentv(), 2003],

round(sales_amount[currentv(), 2003] * 1.25， 2), 0)

8.2.8.4 使用ignore nav 和 keep nav

此关键词使用在model后面；

ignore nav返回值如下：

●空值或缺失数字值时返回0

●空值或缺失字符串值时返回空值字符串

●空值或缺失日期值时返回01-jan-2000。

●其它所有数据库类型时返回空值

keep nav对空值或缺失数字值返回空值,默认条件；

select .....

from table

model ignore nav

paratition by ....

dimension by ....

measures .......

8.2.8.5更新已有的单元

默认情况下，如果表达式左端的引用单元存在，则更新该单元。如果该单元不存在，就在数组中创建一条新的记录。可以用rules update 改变这种默认行为，支出在单元不存在的情况下不创建新纪录；

为了验证效果，我们引用本文开头的代码：

select

prd_type_id, year, month, sales_amount

from all_sales

where prd_type_id between 1 and 2 and emp_id = 21

model

partition by (prd_type_id)

dimension by (month, year)

measures (amount sales_amount)

rules update (

sales_amount[1, 2004] = sales_amount[1, 2003],

sales_amount[2, 2004] = sales_amount[2, 2003] + sales_amount[3, 2003],

sales_amount[3, 2004] = round(sales_amount[3, 2003] * 1.25, 2)

)

order by prd_type_id, year, month;

8.2.8.6 model配合聚合函数使用

Model子句还可以配合avg、sum、max等函数一起使用

实例1

--在求库存的基础上,增加平均库存及最大销售额

SQL> select prod ,

2 year ,

3 week ,

4 sale , --销售量

5 receipts, --进货量

6 inventory, --库存量

7 avg_inventory , --平均库存

8 max_sale --单周最大销售额

9 from sales_fact

10 where 1 = 1

11 model return updated rows

12 partition by(prod)

13 dimension by(year,week)

14 measures(0 inventory,sale,receipts,0 avg_inventory,0 max_sale) rules automatic

15 order(inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)],

16 avg_inventory[year,ANY] = round(avg(inventory)[cv(year),week],2),

17 max_sale[year,ANY] = max(sale)[cv(year),week]

18 )

19 order by prod,year,week;

PROD YEAR WEEK SALE RECEIPTS INVENTORY AVG_INVENTORY MAX_SALE

-------------------- ---------- ---------- ---------- ---------- ---------- ------------- ----------

口罩 2020 1 100 200 100 4392.86 100000

口罩 2020 2 100 200 200 4392.86 100000

口罩 2020 3 150 300 350 4392.86 100000

口罩 2020 4 1000 5000 4350 4392.86 100000

口罩 2020 5 2000 10000 12350 4392.86 100000

口罩 2020 6 3000 0 9350 4392.86 100000

口罩 2020 7 5000 0 4350 4392.86 100000

口罩 2020 8 10000 10000 4350 4392.86 100000

口罩 2020 9 100000 100000 4350 4392.86 100000

口罩 2020 10 100000 100000 4350 4392.86 100000

口罩 2020 11 100000 100000 4350 4392.86 100000

口罩 2020 12 100000 100000 4350 4392.86 100000

口罩 2020 13 100000 100000 4350 4392.86 100000

口罩 2020 14 100000 100000 4350 4392.86 100000

14 rows selected

————————————————

原文链接：https://blog.csdn.net/u010520724/article/details/106047206

8.3 PIVOT & UNPIVOT

PIVOT适用：行转列

UNPIVOT适用：列转行

8.3.1 pivot

实例1：了解每种产品在各个月的销售趋势

select --用decode

prd_type_id,

sum(decode(month, 1, amount, 0)) as JAN,

sum(decode(month, 2, amount, 0)) as FEB,

sum(decode(month, 3, amount, 0)) as MAR,

sum(decode(month, 4, amount, 0)) as APR

from all_sales

where prd_type_id in(1, 2, 3) group by prd_type_id;

-------------------------------------------------------

select --用case when

prd_type_id,

sum(case when month = 1 then amount else 0 end) as JAN,

sum(case when month = 2 then amount else 0 end) as FEB,

sum(case when month = 3 then amount else 0 end) as MAR,

sum(case when month = 4 then amount else 0 end) as APR

from all_sales

where prd_type_id in (1, 2, 3)

group by prd_type_id;

————————————————

select * --用pivot

from (

-- ①

select month, prd_type_id, amount

from all_sales

where year = 2003

and prd_type_id in(1, 2, 3)

)

pivot(

-- ②

sum(amount) for month in (1 as JAN, 2 as FEB, 3 as MAR， 4 as APR)

)

order by prd_type_id;

Pivot的语法：

Select *

From （

inner_query）--内部查询

Pivot（

Aggregate_function --聚合函数

For pivot_column --原表字段名，该字段的值被转换为多字段

IN (list_of_values)--该字段的值，别名重新命名

）

Order by ...;--一般为主键

以上语法将原表（内部查询）通过pivot行转列，将原pivot_column字段的值划分为多个字段且加了别名

8.3.2 转换多列

将for后面规定的哪些行转列由单字段改为多字段

实例：按照(month, prd_type_id) 转换

select *

from (

select month, prd_type_id, amount

from all_sales

where year = 2003 and prd_type_id in(1,2,3)

)

pivot (

sum(amount) for(month, prd_type_id) in(

(1, 1) as jan_prd_type_1,

(2, 2) as feb_prd_type_2,

(3, 3) as mar_prd_type_3,

(4, 2) as apr_prd_type_2

)

);

8.3.3 在转换中使用多个聚合函数

select *

from (

select month, prd_type_id, amount

from all_sales

where year = 2003

and prd_type_id in(1, 2, 3)

)

pivot (

sum(amount) as sum_amount,

avg(amount) as avg_amount

for(month) in(

1 as JAN, 2 as FEB

)

order by prd_type_id;

值得注意的是系统会自动将两个别名合为一个；

8.3.4 unpivot子句

unpivot子句的作用是将列转换为行。

现有表格数据如下：

select * from pivot_sales_data

unpivot(

amount --列转行后的原字段值的新字段名

for month --列转行后的原字段名的新字段名

in (jan, feb, mar, apr)--需要转换的原字段名

)order by prd_type_id; --一般为主键

————————————————

原文链接：https://blog.csdn.net/E_xiake/article/details/52840696原文链接：https://blog.csdn.net/E_xiake/article/details/52840696

第十三章数据库对象

CREATE OR REPLACE TYPE

(54条消息) oracle 自定义类型 type / create type_weixin_33841503的博客-CSDN博客

(54条消息) 数据库高级学习笔记--对象类型_萍果馅是年糕的博客-CSDN博客_对象类型

第十四章集合

14.1.1 集合简介

变长数组

嵌套表

(54条消息) 【Oracle】嵌套表_Do_GH的博客-CSDN博客_oracle 嵌套表

嵌套表上的MULTISET操作符

cast multiset table

Oracle中CAST函数使用简介

(54条消息) Oracle中CAST函数使用简介_coco3600的博客-CSDN博客

关联数组（索引表）

NellieXU

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

《精通oracle database 12c编程》

第一章 简介

第二章 检索信息

第四章 使用单行函数

第五章 时间值函数

第六章 子查询

第七章 高级查询

第八章 分析函数

8.1.1 评级函数

8.1.1.1排名函数

8.1.2反百分位函数

8.1.3窗口函数

8.1.3.1 求和

8.1.3.2平均值

8.1.3.3 FIRST_VALUE和LAST_VALUE

8.1.3.4 NTH_VALUE（n）函数

8.1.4报表函数

8.1.4.1总计报表

8.1.4.2 RATIO_TO_REPORT

8.1.5 listagg 函数

8.1.6 LAG函数和LEAD 函数

8.1.7 FIRST 函数和LAST函数

8.1.8 线性回归函数

8.1.9 使用假想评级与分布函数

8.2 MODEL 子句

8.3 PIVOT & UNPIVOT

第十三章 数据库对象

第十四章 集合

第一章简介

第二章检索信息

第四章使用单行函数

第五章时间值函数

第六章子查询

第七章高级查询

第八章分析函数

第十三章数据库对象

第十四章集合