06oracle之条件表达式（case、decode）、多行函数、嵌套函数、分组子句的要求、Where和having的选择

条件表达式：

条件表达式的作用是：在SQL语句中使用判断的逻辑（类似于IF-THEN-ELSE）来呈现个性化的数据。
条件判断语句有两种：

1. CASE 表达式：SQL99的语法，类似Basic，比较繁琐
2. DECODE 函数：Oracle自己的语法，类似Java，比较简单

其中：

• Decode函数
• 也可以理解为解码翻译函数。
  

语法解释：
decode (字段名,要翻译的原始值1，翻译后的值1，…，其他不满足翻译条件的默认值)

示例：

--需求:要将工种job的英文转换为中文
 SELECT ename,job, 
 	DECODE(job,'CLERK','职员','SALESMAN','销售人员','MANAGER','经理','其他工种')
 FROM emp;


比如人的性别：一般数据库存放的是：0和1，2，在直接出报表的时候，就需要转换显示。
    SELECT NAME 姓名,
    	DECODE(sex,1,'男',0,'女','人妖') 性别 
    FROM TABLE;

• Case子句：
  
  语法解释：
  case 字段 when 要翻译的值 then 翻译的结果
  when 要翻译的值 then 翻译的结果
  …
  else 默认的结果值
  end；

示例：

SELECT * FROM emp;
--需求:要将工种job转换为中文
SELECT t.ename,
CASE job WHEN 'CLERK' THEN '办事员'
           WHEN 'SALESMAN' THEN '销售人员'
   ELSE '其他人员'  
END
FROM emp t;
--两种语法--第二种很复杂。。。。---虽然复杂但灵活
SELECT t.ename,
CASE WHEN job='CLERK' THEN '办事员'
  WHEN job='SALESMAN' THEN '销售人员'
   ELSE '其他人员'  
END
FROM emp t;

case子句增强

需求：查看公司员工的工资情况，要求显示员工的姓名、职位、工资、以及工资情况。如果是工资小于1000，则显示“工资过低”，工资大于1000小于5000为“工资适中”，工资大于5000的，则显示“工资过高”：

SELECT ename,job,sal,
  CASE WHEN sal<1000 THEN '工资过低' 
       WHEN sal BETWEEN 1000 AND 5000 THEN '工资适中'
         when sal IS NULL THEN '没工资酱油瓶'
         ELSE '工资太高'
  END
FROM emp;

Decode和Case的使用选择：

在Oracle中，翻译值的这种条件判断，优先使用decode，因为简单明了，且Oracle有一定的优化；
更复杂的条件判断或者其他的关系型数据库，只能使用Case子句。

多行函数：

• 多行函数也称之为分组函数、聚集函数。
• 简答的说就是把多行的值汇聚计算成一个值。
  常见的分组(聚集)函数：
  count、max、min、sum、avg

Count的使用注意点：

count统计时可以使用不同的对象：*,column,1，不同的对象统计的方式和效率都不同。
示例：

--需求：统计员工的数量，要求使用count的多种统计方式，并分析原因。
SELECT COUNT(*) FROM emp;--效率最低，全表全字段扫描
SELECT COUNT(empno) FROM emp; --按照主键列来统计--效率也挺高，语法角度来说，不通用
SELECT COUNT(1) FROM emp;--统计的是字符是1的这一列，效率高（原因，这一列只有一个字符，运算的
时候，数据流很小，而且是固定列）
SELECT 1,ename FROM emp;
SELECT COUNT(11111111111) FROM emp;--统计的参数不是列号

嵌套函数：

distinct可用来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回显示不重记录的所有值。因此，一般和count配合使用，作为统计非空且不重复的记录数。

示例：

--查看有几个部门，通过emp表
SELECT Distinct(deptno) FROM emp;
SELECT COUNT(distinct(deptno)) FROM emp;--可去空

--需求：查询公司发放了几种数量的奖金，要求员工是有奖金的，且奖金都不重复。
SELECT COUNT(Distinct(comm)) FROM emp;

注意：
DISTINCT关键字效率会比较低，如果仅仅是为了显示不重复的记录，建议使用group by；
原因是：distinct只有用二重循环查询来解决，而这样对于一个数据量非常大的表来说，无疑是会直接影响到效率的。

分组子句的要求：

在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。
反之，包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

示例：

--需求1：查询显示各个部门的平均薪资情况，并且按照部门号从低到高排列。
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ORDER BY deptno;
--需求2：查询显示各个部门的不同工种的平均薪资情况，并且按照部门号从低到高排列
SELECT deptno,job, AVG(sal) FROM emp GROUP BY deptno,job ORDER BY deptno; 
优化：
select 
   deptno, job, avg(sal)
   from emp
 group by deptno, job
 having deptno in (select deptno from emp where deptno is not null)
 order by deptno desc;

Where和having的选择：

1. 语法上的不同选择

是否能使用组函数的区别： 不能在 WHERE 子句中使用组函数（注意），即where子句不能完全代替having子句。
可以在 HAVING子句中使用组函数。（having可以使用任何的条件写法）

2. HAVING子句不能离开GROUP BY子句单独使用，HAVING子句无法完全代替WHERE子句。

• 分析：

1. 使用having子句过滤，是先分组，再过滤，注意：分组的时候是全表扫描的，效率较低。
2. 使用where子句过滤，是先过滤再分组，注意：分组的时候仅需要扫描部分数据，效率较高。

结论：

• 从语法上看：

• 两者选择简单归纳为，就是group by分组之后需要的条件中有组函数的，就必须得用having，其他都可以直接用where。

• 从性能上看：

• 实际开发中，使用分组的时候尽量先加一个where的过滤条件。没有组函数的情况下，尽量选择where。