Oracle 中 start with 递归查询、case when 条件表达式、rowid 伪列去重

逃离我推掉我的手 2021-12-12 14:05 393阅读 0赞

**目录**

递归查询·需求分析

递归查询·准备数据

递归查询实现·start with...connect by prior

case when 条件表达式

rowid 伪列删除表中重复数据

--------------------

# 递归查询·需求分析 #

1、开发中经常会有这种需求实现，如下所示是 [JQuery-Ztree 树插件的效果][JQuery-Ztree]：

![20190711145651270.gif][]

2、如上所示的 [前端 tree ][JQuery-Ztree]需要的数据格式如下（关系也很简单，就是自己的 id 以及显示的 name，与自己父节点 id）：

var zNodes =[
        { id:1, pId:0, name:"湖南省", open:true},
        { id:11, pId:1, name:"长沙市"},
        { id:111, pId:11, name:"芙蓉区"},
        { id:112, pId:11, name:"天心区"},
        { id:113, pId:11, name:"岳麓区"},
        { id:114, pId:11, name:"开福区"},
        { id:115, pId:11, name:"雨花区"},
        { id:116, pId:11, name:"望城区"},
        { id:12, pId:1, name:"娄底市"},
        { id:121, pId:12, name:"娄星区"},   
        { id:122, pId:12, name:"冷水江市"},
        { id:123, pId:12, name:"涟源市"},
        { id:124, pId:12, name:"双峰县"},
        { id:125, pId:12, name:"新化县"},
        { id:1251, pId:125, name:"白溪镇"},
        { id:1252, pId:125, name:"洋溪镇"},
        { id:1253, pId:125, name:"吉庆镇"},
        { id:1254, pId:125, name:"曹家镇"},
        { id:2, pId:0, name:"广东省", open:true},
        { id:21, pId:2, name:"深圳市"},
        { id:211, pId:21, name:"罗湖区"}, 
        { id:212, pId:21, name:"福田区"},
        { id:213, pId:21, name:"南山区"},
        { id:214, pId:21, name:"宝安区"},
        { id:215, pId:21, name:"坪山区"},
        { id:216, pId:21, name:"龙岗区"}
    ];

3、本文介绍的重点不是前端如何实现树形菜单的显示，而是后台如何查询数据库这种数据。

# 递归查询·准备数据 #

1、注意根据上面前端树结构的需要，后台返回的数据必须有 id，pId，以及 name，至于 menu\_level（层级） 字段可有可无，可以根据实际业务决定。

2、前端使用的树不同，数据格式可能也会大同小异，如下所示字段名称并没有和上面 tree 插件要求的属性名称完全一样，这只需要查询的 as 一下别名即可。

准备数据脚本：[Oracle 新建地区级联数据.sql][Oracle _.sql]

SQL 运行完成后，Oracle 数据库数据如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70][]

# 递归查询实现·start with...connect by prior #

1、connect by 是结构化查询中用到的，其基本语法是：

<table> 
 <tbody> 
  <tr> 
   <td> <p>select … from tablename where 条件1 start with 条件2 connect by 条件3 order by 列 ;</p> <p>条件1 是过滤条件，用于对返回的所有记录进行过滤筛选<br> 条件2 是根结点/起始节点的限定语句<br> 条件3 是连接条件，用于递归迭代的关联。使用 prior 表示上一条记录，比如 "CONNECT BY PRIOR id = parentId"，表示上/前一条记录的 id 是后一条记录的 parentId</p> </td> 
  </tr> 
 </tbody> 
</table>

2、下面循序渐进式的进行编写 SQL：

--查找树中的所有顶级父节点(1级菜单)
    select * from scott_menu sm where sm.parentId = 0;
    select * from scott_menu sm where sm.menu_level= 1;
    
    --查找某个节点（如id为1）的直属子节点（所有儿子，不包括孙子）
    select * from scott_menu sm where sm.parentId=1;
    
    --查找某个节点下的所有子节点（包括所有子孙后代）。如下所示查询 id=1 的菜单下的所有子孙节点
    select * from scott_menu sm start with sm.id=1 connect by  prior sm.id = sm.parentId;--前一条记录的 id 是后一条记录的 parentId
    
    --查找所有1级菜单下的全部子孙节点，即查询整颗树，这也是实际中最常见的操作，有了上面的基础，现在则轻而易举了。
    --将起始节点设置为所有的一级菜单即可。这查询出来的数据完全符合前端页面的格式，只需要后台再封装成 json 返回给页面即可
    select * from scott_menu sm start with sm.parentId=0 connect by  prior sm.id = sm.parentId;
    select * from scott_menu sm start with sm.menu_level=1 connect by  prior sm.id = sm.parentId;
    
    --如果需要对查询的结果进行过滤，则使用 where 条件进行筛选,并以 id 倒叙
    select * from scott_menu sm where sm.title like '%区%' start with sm.parentId = 0 connect by  prior sm.id = sm.parentId order by id desc;
    
    --需求：查询 "白溪镇" 以及所在的上级 市、省份
    select * from scott_menu sm start with sm.title = '白溪镇' connect by sm.id = prior sm.parentId;
    --起始第一条数据为 '白溪镇'，如何会递归查询下一条的 parentId 等于自己 id 的记录，以此类推
    
    --查询某个节点（如 id= 16）的兄弟节点（亲兄弟，有同一个父节点）
    select * from scott_menu sm where exists (select * from scott_menu sm2 where sm.parentId=sm2.parentId and sm2.id=16);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70 1][]

从上面查询整颗树的结果可知，只需要在后台封装好前端所需要的 json 格式的数据返回，前端即可显示。

# case when 条件表达式 #

1、条件表达式格式（Oracle 与 Mysql 通用的写法）(注意：返回值的数据类型必须一致，即不能返回值1是 number，而返回值2确实 char)：

<table> 
 <tbody> 
  <tr> 
   <td> <p>语法 1:</p> <p>&nbsp;&nbsp; &nbsp; CASE 字段<br> &nbsp;&nbsp; &nbsp; WHEN 条件1 THEN 返回值1<br> &nbsp;&nbsp; &nbsp; WHEN 条件2 THEN 返回值2<br> &nbsp;&nbsp; &nbsp; ...<br> &nbsp;&nbsp; &nbsp; ELSE 返回的默认值&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --没有提供默认值，且上面条件都没有匹配时，返回 null</p> <p>&nbsp;&nbsp;&nbsp;&nbsp; END</p> <p>语法 2:</p> <p>&nbsp;&nbsp; &nbsp; CASE<br> &nbsp;&nbsp; &nbsp; WHEN 条件 1 THEN 返回值1<br> &nbsp;&nbsp; &nbsp; WHEN 条件2 THEN 返回值2<br> &nbsp;&nbsp; &nbsp; ...<br> &nbsp;&nbsp; &nbsp; ELSE 返回的默认值<br> &nbsp;&nbsp; &nbsp; END</p> </td> 
  </tr> 
 </tbody> 
</table>

--方式一。最简单的用法
    select t.stuid,t.stuname,case t.gender when '男' then 1 when '女' then 0 else -1 end from student t;
    --两者结果完全一样
    --方式二，使用条件查询，此时 case 后面不要再加 字段
    select t.stuid,t.stuname,case when t.gender = '男' then 1 when t.gender = '女' then 0 else -1 end from student t;
    
    --如果性别为 "男" 则返回 1，否则返回本来的值。注意返回的数据类型必须一致
    select t.stuid,t.stuname,case when t.gender = '男' then '1' else t.gender end from student t;
    
    -- 薪资(sal) >= 5000 的加薪 10%，小于 5000的加薪 20%
    update emp t set t.sal = case when  t.sal >= 5000 then t.sal * 1.1 else t.sal * 1.2 end;

2、Oracle 独有的函数写法：decode(字段,if1,then1,if2,then2,....)

select * from emp;--查询所有
    --将姓名 "SMITH" 改为 "张无忌"，"ALLEN" 改为 "郭靖"，"WARD" 改为 "李白"，其余的默认为无名
    --case when then 写法是 Oracle 、Mysql 通用的写法
    select case ename 
           when 'SMITH' then '张无忌'
           when 'ALLEN' then '郭靖'
           when 'WARD' then '李白'
           else '无名' end
           from emp;
    --将姓名 "SMITH" 改为 "张无忌"，"ALLEN" 改为 "郭靖"，"WARD" 改为 "李白"，其余的默认为无名
    select decode(ename,'SMITH','张无忌','ALLEN','郭靖','WARD','李白','无名') from emp;

![20190528163421647.gif][]

# rowid 伪列删除表中重复数据 #

1、oracle 数据库的伪列 rowid 表示该条数据在 oracle 数据库中的物理存储位置，值为长度18的字符串(如 AAATRXAAGAAAK1XAAA)。oracle 内部通常就是使用它来访问数据的，它可以唯一标识一行数据。伪列只能查询，不能增删改

2、rowid 伪列默认不显示，像 rownum 一样需要显示指定，如 select t.\*,rowid from student t ;

3、和 rownum 行号不同的是，rowid 不但可以作为 select 的 where 条件，还可以作为 update、delete 等操作的 where 条件，如：delete from student t where t.rowid = 'AAATRXAAGAAAK1XAAA';

4、所以生产中有一个常见的操作就是用 rowid 来删除表中完全重复的数据，下面先准备测试数据：

--创建学生表
    create table STUDENT (
      stuid      VARCHAR2(16) not null,
      stuname    VARCHAR2(16) not null,
      gender     VARCHAR2(2) not null,
      age        NUMBER(8) not null,
      stuaddress VARCHAR2(50),
      enrolldate DATE
    );
    
    --插入数据
    insert into student values('1','张三丰','男',108,'武当派开山祖师',to_date('2019-08-25 09:25:33','yyyy-mm-dd hh24:mi:ss'));
    insert into student values('1','张三丰','男',108,'武当派开山祖师',to_date('2019-08-25 09:25:33','yyyy-mm-dd hh24:mi:ss'));
    insert into student values('2','郭襄','女',56,'峨嵋派开山祖师',to_date('2015-06-25 15:00:33','yyyy-mm-dd hh24:mi:ss'));
    insert into student values('2','郭襄','女',56,'峨嵋派开山祖师',to_date('2015-06-25 15:00:33','yyyy-mm-dd hh24:mi:ss'));
    insert into student values('3','杨不悔','女',27,'明教右使千金',to_date('2020-09-21 11:45:20','yyyy-mm-dd hh24:mi:ss'));

删除表中重复数据行方式一（推荐方式）：rowid

--根据单个字段(stuid)进行分组，然后删除重复数据，min(rowid) 表示保留最先入库的数据，max(rowid) 表示保留最后入库的数据
    delete from student where 
    stuid in ( select stuid from student group by stuid having count(*) > 1) 
    and 
    rowid not in (select min(rowid) from student group by stuid having count(*) > 1);
    
    --根据多个字段进行分组，然后删除重复数据，min(rowid) 表示保留最先入库的数据, max(rowid) 表示保留最后入库的数据
    delete from student where 
    (stuid,stuname) in ( select stuid,stuname from student group by stuid,stuname having count(*) > 1) 
    and 
    rowid not in (select min(rowid) from student group by stuid,stuname having count(*) > 1);

删除表中重复数据行方式二：先取后删再插

--先使用 distnct 关键字进行去重查询，去除结果集中重复的数据行。如果需要对整个表进行去重，则省略 where 条件即可！
    select distinct * from student t where t.stuid < 3 order by stuid;
    --新建临时表(student_temp)，并将去重结果存入进去
    --使用 order by 关键字的目的是让后面从临时表再重新插回目标表的时候，数据仍然保持和原来一样的顺序.
    create table student_temp as select distinct * from student t where t.stuid < 3 order by stuid;
    delete from student t where t.stuid < 3;--然后删除目标表（student）中的所有重复数据
    insert into student select * from student_temp;--最后将临时表(student_temp)的数据再插入到目标表(student)中.
    drop table student_temp;--删除临时表 student_temp

![20200407195333266.gif][]

[删除指定单位指定人员类型下重复的身份证人员,保留最后入库的人员数据。][Link 1]

[JQuery-Ztree]: https://wangmaoxiong.blog.csdn.net/article/details/80077963#zTree%20%E7%AE%80%E5%8D%95%E6%95%B0%E6%8D%AE%E6%A0%BC%E5%BC%8F
[20190711145651270.gif]: /images/20211211/13572d0e5c91482792177f1b0d8566d1.png
[Oracle _.sql]: https://gitee.com/wangmx1993/my-document/blob/master/sql/Oracle%20%E6%96%B0%E5%BB%BA%E5%9C%B0%E5%8C%BA%E7%BA%A7%E8%81%94%E6%95%B0%E6%8D%AE.sql
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70]: /images/20211211/1e7b5ccca6e54e6cb683f60758212c43.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70 1]: /images/20211211/be7efff546fe45d59b115d424b397525.png
[20190528163421647.gif]: /images/20211211/6cf16a71add94290ac59c567660f6ba4.png
[20200407195333266.gif]: /images/20211211/c5e8cebf932b4b6188fd84a1ca728aac.png
[Link 1]: https://gitee.com/wangmx1993/material/commit/63923a280f011b4b9aafbb684743ca7735a0733e