Oracle基础(十一)高级查询

高级查询

集合操作 : 将两个或多个查询返回的结构结合起来
union,union all,intersect,minus

并集(获取的结果集合并到一个结果集中)

	SQL> select * from dept where deptno > 10
		  2  union all
		  3  select * from dept where deptno >30
		  4  ;
		
		    DEPTNO DNAME          LOC
		---------- -------------- -------------
		        20 RESEARCH       DALLAS
		        30 SALES          CHICAGO
		        40 OPERATIONS     BOSTON
		        50 b              a
		        40 OPERATIONS     BOSTON
		        50 b              a
		
		已选择6行。
		
		SQL> select * from dept where deptno > 10
		  2  union
		  3  select * from dept where deptno >30
		  4  ;
		
		    DEPTNO DNAME          LOC
		---------- -------------- -------------
		        20 RESEARCH       DALLAS
		        30 SALES          CHICAGO
		        40 OPERATIONS     BOSTON
		        50 b              a

并集:
SQL> select * from dept where deptno > 10
2 intersect
3 select * from dept where deptno >30
4 ;

			    DEPTNO DNAME          LOC
			---------- -------------- -------------
			        40 OPERATIONS     BOSTON
			        50 b              a

差集:
SQL> select * from dept where deptno > 10
2 minus
3 select * from dept where deptno >30;

		    DEPTNO DNAME          LOC
		---------- -------------- -------------
		        20 RESEARCH       DALLAS
		        30 SALES          CHICAGO

case表达式
case search_expression
when expression1 then result1
when expression2 then result2
…
when expression then result
else default_result;

 员工工资小于1000
		1000~2000
		>2000

low  med  high
3	  8		10


SQL> select empno,ename,
	  2  case deptno
	  3  when 10 then 'A'
	  4  when 20 then 'B'
	  5  when 30 then 'C'
	  6  else 'not exists'
	  7  end
	  8  from emp;
	
	     EMPNO ENAME      CASEDEPTNO
	---------- ---------- ----------
	      7369 SMITH      A
	      7499 ALLEN      C
	      7521 WARD       C
	      7566 JONES      B
	      7654 MARTIN     C
	      7698 BLAKE      C
	      7782 CLARK      A

SQL> select
  2  count(case when sal<1000 then 1 else null end) low,
  3  count(case when sal between 1000 and 2000 then 1 else null end) med,
  4  count(case when sal>2000 then 1 else null end) high
  5  from emp;

       LOW        MED       HIGH
---------- ---------- ----------
         2          6          6

一行数据展示出来所有部门的人数(列转行)

	select * from 
	(select deptno,count(1) c from emp group by deptno) t
	pivot (min(c) for deptno in(10,20,30,40,50))