DECODE函数
-
语法:DECODE(expr,serch1,result1,serch1,result2…,default)
-
DECODE用于比较参数expr的值,如果匹配到哪一个search条件,就返回对应的result结果
-
可以有多组search和result的对应关系,如果任何一个search条件都没有匹配到,则返回最后的default的值
-
default参数是可选的,如果没有提供default参数值,且没有匹配到时,将返回NULL
-
DECODE函数可以在GROUP BY语句中使用,可以将字段值不同的记录分成一组
-
DECODE函数可以在ORDER BY语句中使用,可以按照字段内容排序,但是有局限性
CASE语句
-
和DECODE函数功能相似的有CASE语句,实现类似于if-else
-
语法:CASE expr WHEN search1 THEN result1 WHEN search2 THEN result2…ELSE default END
-
语法理解参照DECODE函数
排序函数
-
它允许对结果集按照指定的字段分组,在组内再按照指定的字段排序,最终生成组内编号
-
语法:排序函数类型 OVER(PARTITION BY 字段1 ORDER BY 字段2)
-
字段1:按照给定字段分组,字段2:按照给定字段排序
排序函数:ROW_NUMBER 函数
-
生成组内连续且唯一的数字
-
语法:ROW_NUMBER() OVER(PARTITION BY 字段1 ORDER BY 字段2)
排序函数:RANK 函数
-
生成组内不连续也不唯一的数字,同样排序字段值一样的记录,生成的数字也一样
-
语法:RANK() OVER(PARTITION BY 字段1 ORDER BY 字段2)
排序函数:DENSE_RANK 函数
-
生成组内连续但不唯一的数字
-
语法:RANK() OVER(PARTITION BY 字段1 ORDER BY 字段2)
集合操作
-
为了合并多个SELECT语句的结果,可以使用集合操作符,实现集合的并,交,差
-
集合操作符包含以下4种:
-
UNION、UNION ALL:并集,UNION会去除两个结果集的重复的记录,UNION ALL则不会
-
INTERSECT:交集,使用INTERSECT操作符后的结果集会以第一列的数据做升序排列
-
MINUS:差集,只有在第一个结果中存在,在第二个结果集中不存在的数据,才能够被显示出来,也就是结果集一减去结果集二的结果
-
-
语法:SELECT statement1 UNION|UNION ALL|INTERSECT|MINUS SELECT statement2…
-
多个结果集合并,它们列的个数和数据类型需要匹配,不匹配的时候,可以使用NULL来代替不匹配的字段,那么该字段的值都为NULL
-
ORDER BY 子句只能放在最后一个查询语句中
高级分组函数
- 高级分组函数都是GROUP BY子句的扩展,当我们想组合多个分组查询的结果集时,就可以使用高级分组函数
高级分组函数:ROLLUP
-
分组原则:逐次递减,一直到所有参数都不要,每一种分组都统计一次并且在一个结果集显示;
-
用法:GROUP BY ROLLUP(a,b,c…),a,b,c为要逐个递减的字段
-
等价于:SELECT xxx GROUP BY(a,b,c) UNION ALL SELECT xxx GROUP BY(a,b) UNION ALL SELECT xxx GROUP BY(a) UNION ALL 全表
-
分组次数:参数个数 + 1
高级分组函数:CUBE:
-
分组原则:每种组合分一次组
-
用法:GROUP BY CUBE(a,b…),a,b为要逐个递减的字段
-
等价于:SELECT xxx GROUP BY(a,b) UNION ALL SELECT xxx GROUP BY(a) UNION ALL SELECT xxx GROUP BY(b) UNION ALL 全表
-
分组次数:2的参数个数次方
高级分组函数:GROUPING SETS
-
分组原则:每种参数是一种分组,然后将这些分组统计后并在一个结果集显示
-
用法:GROUP BY GROUPING SETS((a),(b))
-
等价于:SELECT xxx GROUP BY(a) UNION ALL SELECT xxx GROUP BY(b)
演示:查询函数
-
DECODE
- 根据职位计算奖励金额,当职位分别是‘MANAGER’、‘ANALYST’、‘SALESMAN’时,奖励金额分别是薪水的1.2倍,1.1倍,1.05倍,如果不是这三个职位,则奖励金额取薪水值
SELECT ename,job,sal,
DECODE(job,
‘MANAGER’,sal1.2,
‘ANALYST’,sal1.1,
‘SALESMAN’,sal*1.05,
sal
) bonus
FROM emp;
- 根据职位计算奖励金额,当职位分别是‘MANAGER’、‘ANALYST’、‘SALESMAN’时,奖励金额分别是薪水的1.2倍,1.1倍,1.05倍,如果不是这三个职位,则奖励金额取薪水值
-
在GROUP BY语句中使用DECODE函数
- 计算职位人数,将职位是‘MANAGER’、‘ANALYST’看作一组,其余职业看作一组,分别统计人数
SELECT COUNT(*),DECODE(job,‘MANAGER’,‘VIP’,‘ANALYST’,‘VIP’,‘OTHER’)
FROM emp GROUP BY DECODE(job,‘MANAGER’,‘VIP’,‘ANALYST’,‘VIP’,‘OTHER’);
- 计算职位人数,将职位是‘MANAGER’、‘ANALYST’看作一组,其余职业看作一组,分别统计人数
-
在ORDER BY语句中使用DECODE函数
- Dept表中按“OPERATIONS”,“ACCOUNTING”,“SALES”排序,无法按照字面数据排序
SELECT deptno,dname,loc FROM dept
ORDER BY DECODE(dname,‘OPERATIONS’,1,‘ACCOUNTING’,2,‘SALES’,3);
- Dept表中按“OPERATIONS”,“ACCOUNTING”,“SALES”排序,无法按照字面数据排序
-
CASE语句
- 执行“根据职位计算奖励金额”的查询
SELECT ename,job,sal,
(CASE job WHEN ‘MANAGER’ THEN sal1.2
WHEN ‘ANALYST’ THEN sal1.1
WHEN ‘SALESMAN’ THEN sal*1.05
ELSE sal END
) bonus
FROM emp;
- 执行“根据职位计算奖励金额”的查询
-
排序函数:ROW_NUMBER
- 查看每个部门的工资排名(注意WAD,MARTIN,JAMES员工记录的RANK值)
SELECT ename,sal,deptno,
ROW_NUMBER() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp;
- 查看每个部门的工资排名(注意WAD,MARTIN,JAMES员工记录的RANK值)
-
排序函数:RANK
- 注意WAD,MARTIN,JAMES员工记录的RANK值
SELECT ename,sal,deptno,
RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp;
- 注意WAD,MARTIN,JAMES员工记录的RANK值
-
排序函数:DENSE_RANK
- 注意WAD,MARTIN,JAMES员工记录的RANK值
SELECT ename,sal,deptno,
DENSE_RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp;
- 注意WAD,MARTIN,JAMES员工记录的RANK值
-
集合操作:UNION
- 合并职位是’MANAGER’的员工和薪水大于2500的员工集合
SELECT ename,job,sal FROM emp WHERE job=‘MANAGER’
UNION
SELECT ename,job,sal FROM emp WHERE sal>2500;
- 合并职位是’MANAGER’的员工和薪水大于2500的员工集合
-
集合操作:UNION ALL
SELECT ename,job,sal FROM emp WHERE job=‘MANAGER’
UNION ALL
SELECT ename,job,sal FROM emp WHERE sal>2500; -
集合操作:INTERSECT
- 查看工资高于2500且工作是’MANAGER’的员工
SELECT ename,sal,job FROM emp WHERE sal>2500
INTERSECT
SELECT ename,sal,job FROM emp WHERE job=‘MANAGER’;
- 查看工资高于2500且工作是’MANAGER’的员工
-
集合操作:MINUS
- 列出职位是MANAGER但是薪水低于2500的员工记录
SELECT ename,sal,job FROM emp WHERE job=‘MANAGER’
MINUS
SELECT ename,sal,job FROM emp WHERE sal>=2500;
- 列出职位是MANAGER但是薪水低于2500的员工记录
-
素材,该表是存储某个商店在某年某月某日的销售额
CREATE TABLE sales_tab(
year_id NUMBER NOT NULL,
month_id NUMBER NOT NULL,
day_id NUMBER NOT NULL,
sales_value NUMBER(10,2) NULL
);
INSERT INTO sales_tab
SELECT TRUNC(DBMS_RANDOM.value(2010,2012)) AS year_id,
TRUNC(DBMS_RANDOM.value(1,13)) AS month_id,
TRUNC(DBMS_RANDOM.value(1,32)) AS day_id,
TRUNC(DBMS_RANDOM.value(1,100),2) AS sales_value
FROM dual
CONNECT BY level <=1000; -
查看每天总营业额
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
GROUP BY year_id,month_id,day_id ORDER BY year_id,month_id,day_id; -
查看每个月总营业额
SELECT year_id,month_id,SUM(sales_value) FROM sales_tab
GROUP BY year_id,month_id ORDER BY year_id,month_id; -
查看每年总营业额
SELECT year_id,SUM(sales_value) FROM sales_tab
GROUP BY year_id ORDER BY year_id; -
查看总营业额
SELECT SUM(sales_value) FROM sales_tab;
-
当想把上面的结果显示在一张表中,可以使用UNION ALL
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
GROUP BY year_id,month_id,day_id
UNION ALL
SELECT year_id,month_id,NULL,SUM(sales_value) FROM sales_tab
GROUP BY year_id,month_id
UNION ALL
SELECT year_id,NULL,NULL,SUM(sales_value) FROM sales_tab
GROUP BY year_id
UNION ALL
SELECT NULL,NULL,NULL,SUM(sales_value) FROM sales_tab; -
高级分组函数:ROLLUP
- 查看每天,每月,每年,以及总营业额
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
GROUP BY ROLLUP(year_id,month_id,day_id);
- 查看每天,每月,每年,以及总营业额
-
高级分组函数:CUBE
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
GROUP BY CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id; -
高级分组函数:GROUPING SETS
- 仅查看每天与每月的营业额
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id)
- 仅查看每天与每月的营业额