Oracle行转列、数据合并、递归用法

一、Oracle--case when 行转列

case when 语法结构

case 列名
when 条件值1 then 选项1
when 条件值2 then 选项2
.......
else   默认值 end

复制

案例

--  Oracle 行列转换
    -- 数据，使用scott用户的emp表数据
    select * from scott.emp t where rownum<=50;
    -- 表字段说明：
    -- empno,    ename,   job,  mgr,     hiredate, sal, comm, deptno
    -- 员工编号，员工姓名，岗位，领导编号，入职日期，薪资，奖金，部门编号
 
-- 需求：查询scott.emp表中，每个部门的人数，并进行行转列显示
select
max(case when deptno=10 
    then total else 0 end) "10",
max(case when deptno=20 
    then total else 0 end) "20",
max(case when deptno=30 
    then total else 0 end) "30" 
from 
  (select 
      deptno,
      count(*) total 
  from scott.emp 
  group by deptno
  );

复制

二、Oracle数据合并（存在则更新，不存在就插入）

-- Merge into
MERGE INTO table_name alias1 
USING  table|view|sub_query alias2
ON (join condition) 
WHEN MATCHED THEN 
  UPDATE SET 
  col1 = col_val1, 
  col2 = col2_val 
WHEN NOT MATCHED THEN 
  INSERT (column_list) 
  VALUES (column_values); 

复制

案例：

---创建表
create table jalen.test_merge_A(
empno number,
ename varchar2(30),
sal number);
---创建表
create table jalen.test_merge_B(      
empno number,
ename varchar2(30),
sal number);
 
------插入数据
insert into test_merge_A values(1122,'AA',1500);
insert into test_merge_A values(1133,'BB',1600);
insert into test_merge_A values(1144,'CC',1700);
insert into test_merge_A values(1155,'DD',1800);
 
insert into test_merge_B values(1144,'DD',2500);
insert into test_merge_B values(1166,'EE',5000);
insert into test_merge_B values(1177,'FF',3000);
insert into test_merge_B values(1122,'AA',3000);
-----查看数据
select * from test_merge_A;
select * from test_merge_B;
 
--将B表的数据合并到A表，如果存在则更新，如果不存在则插入
merge into test_merge_A a
using test_merge_B b
on (a.empno=b.empno)
when matched then 
update set a.ename=b.ename,a.sal=b.sal 
when not matched then 
insert (a.empno,a.ename,a.sal) 
  values (b.empno,b.ename,b.sal);
 
-----查看数据
select * from test_merge_A;

复制

三、Oracle递归用法

递归语法:

递归语法:
select 
  [level],
  column,
  expr,... 
from table
[where condition]
start with condition
connect by [prior column1 = column2   向下
          |column1 = prior  column2];  向上

复制

• level:伪列。用于返回层次查询的层次(1:根行 2:第2级行 3:第3级行...)
• start with:用于指定层次关系查询的根行 ；决定了爬树的起点
• connect by:用于指定父行和子行的关系 当定义父行和子行的关系时,必须使用prior关键字，决定了爬树的方向:
• prior:用于指定哪个是父级列

案例：

-- Oracle 递归查找
  -- 数据，使用scott用户的emp表数据
  select * from scott.emp t
  where rownum<=50;
  -- 表字段说明：
  -- empno,ename,job, mgr, hiredate, sal, comm, deptno
  -- 员工编号，员工姓名，岗位，领导编号，入职日期，薪资，奖金，部门编号
 
-- 需求1：向上递归查出empno=7369的所有上级领导的编号和姓名
   -- 7369-->7902-->7566--> 7839
select 
  empno,
  ename 
from scott.emp
start with empno = 7369   
connect by empno = prior mgr;
 
-- 需求2：向下递归查出7839的所有‘10’部门的下属及层级
select 
  empno,
  ename,
  deptno 
from scott.emp
where deptno = 10
start with empno = 7839 
connect by prior empno = mgr;

复制