Oracle高级查询

Oracle高级查询

1. count(*) 和 count(column) 的区别

   • 如果该列的值有空值，则两个的结果不一样，count(column)只会统计非空的数量
   • 如果该列的值没有空值，则两个的结果一样

2. wm_concat函数的使用（分组函数）

   行转列，比如按员工部门分组后，对员工姓名进行行转列后，得到的值为：张三，李四，王五

   即所有行的值拼接起来，中间用逗号隔开

3. where 与 having 的区别

   

   在没有分组函数的情况下，优先使用where，效率更高

   因为where会先过滤再分组，而having是先分组再过滤

4. group by语句的增强（用在报表里会非常有用）

SELECT C_APPL_TYPE ,PERMIT_RPT_QUEUE,count(C_APPL_TYPE) FROM Sys_Machine_Setting t GROUP BY C_APPL_TYPE ,PERMIT_RPT_QUEUE;
SELECT C_APPL_TYPE,count(C_APPL_TYPE) FROM Sys_Machine_Setting t GROUP BY C_APPL_TYPE;
SELECT count(C_APPL_TYPE) FROM Sys_Machine_Setting t;

上面3个语句的并集等价于下面的语句：

SELECT C_APPL_TYPE ,PERMIT_RPT_QUEUE,count(C_APPL_TYPE) FROM Sys_Machine_Setting t GROUP BY ROLLUP(C_APPL_TYPE,PERMIT_RPT_QUEUE);

使用rollup函数

语法规则：

group by rollup(a,b)

等价于

group by a,b

+

group by a

+

group by null

5. 自连接存在的问题

• 不适合操作大表

• 解决办法：层次查询

  某些情况下，可以取代自连接

  本质上，是一个单表查询

  层次查询示例：

  select level,empno,ename,sal,mgr
  from emp
  connect by prior empno=mgr
  --connect by 上层的员工号=老板号
  start with mgr is null
  order by 1;
  --start with empno=7839 (7839为根节点的员工号), 与 start with mgr is null 是等价的
  
  SELECT
  	LEVEL ,t.*
  FROM
  	MENU_FUNCTION t
  CONNECT BY
  	PRIOR FUNCTION_ID = MENU_PARENT_ID
  START WITH
  	FUNCTION_ID = 'SPP000'
  ORDER BY
  	LEVEL ;
  

6. 子查询需要注意的问题

• 可以使用子查询的位置

  where, select, having, from

• 不可以使用子查询的位置

  group by

• 主查询与子查询的执行顺序

  一般先执行子查询，再执行主查询

  但是相关子查询例外，先执行主查询，再执行子查询

  示例：找到员工表中薪水大于本部门平均薪水的员工

   select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
   from emp e
   where sal > (select avg(sal) from emp where deptno=e.deptno);
  

• 单行子查询和多行子查询

  • 单行子查询只能连接单行操作符
  • 多行子查询只能连接多行操作符

  多行操作符：

  in,any, all

• 子查询中的空值问题

  多行子查询中的空值示例：查询不是老板的员工

  如果mgr存在空值，那么如下的sql查询是有问题的：

   select * from emp where empno not in (select mgr from emp);
  

  正确的写法应该为：

   select * from emp where empno not in 
      (select mgr from emp where mgr is not null);