SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH

这篇具有很好参考价值的文章主要介绍了SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

场景描述

场景一:员工信息表,根据某一员工编号查找其上级及上级的上级,直至最高权力者;或者下级及下级的下级,直至其管理下的最基层员工。
场景二:机构树,查询某一机构所对应的上级机构,直至最高机构;或者下级机构直至最末级机构
场景三:号码更换(银行卡换号不换卡),给出某一号码,查找该卡历史所有换号信息,给出的号码可能是历史的、也可能是最新的。

一.Orcale递归查询 start with

构建表并插入数据,orcale的insert into只能一条一条数据插入

drop table if exists tree_tb;
create table tree_tb(
	employee int,
	leader int,
	levels varchar(10)
);

insert into tree_tb values (1001,1005,'L1');
insert into tree_tb values (1001,1005,'L1');
insert into tree_tb values (1002,1005,'L1');
insert into tree_tb values (1005,1008,'L2');
insert into tree_tb values (1008,1010,'L3');
insert into tree_tb values (1010,null,'L4');

执行第一个start with 递归查询

select * from tree_tb
start with employee=1005
connect by prior leader=employee
;

递归的执行机制1:
首先 from 表,然后 start with 执行筛选,得到开始的数据(例如employee=1005,leader=1008,levels=‘L2’);
第一次递归 connect by ,prior 的字段是 上一次查询的结果表 t1的leader=原表 t2的employee,即 1008=employee 执行查询得到结果(mployee=1008,leader=1010,levels=‘L3’);
第二次递归 connect by,同上,prior 的字段是 上一次查询的结果表 t2的leader=原表 t3的employee,即 1010=employee 执行查询得到结果(mployee=1010,leader=null,levels=‘L4’);
第三次递归 connect by,同上,prior 的字段是 上一次查询的结果表 t3的leader=原表 t4的employee,即 null=employee 执行查询未得到任何结果,至此递归结束;
所以,最终查询结果如下图

SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH
当 connect by 条件反过来时,递归方向发生变化,逻辑依然同上述过程,不赘述。

select * from tree_tb
start with employee=1005
connect by prior employee=leader
;

SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH
下方分别添加 and prior 和 where 并展示了查询结果。
递归的执行机制2:
and prior 同第一个 prior一样,将上次查询的结果用来做匹配,避免多匹多造成的结果错误(下方星环TDH的案例中添加了company字段,能够更好的说明多个and prior的必要性);
where 是对结果值做最后的筛选,执行在语句的最后,由结果可以看出这一点

insert into tree_tb values (1005,1006,'L2');

select * from tree_tb
start with employee=1005
connect by prior leader=employee
	and prior levels=levels
;

select * from tree_tb
where levels <> 'L3'
start with employee=1005
connect by prior leader=employee
;

SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH

SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH

二.Hive递归查询

Hive并没有直接支持递归查询的函数,但是Hive支持Orcale语法(腾讯TDW和星环TDH)时,可以直接使用start with来实现递归查询。下方以星环TDH为例做介绍。

三.星环TDH递归查询 start with

小插曲
星环TDH不能insert into table values()插入数据,会报错
[Hive Error]: Query returned non-zero code: 10, cause: FAILED: Error in semantic analysis: Only allow to single insert into Hyperbase/Transaction Orc/Holodesk, other data destination not allowed。
(亲测hive中并无此限制),TDH中即使改成了orc也不可以,因此采用下方insert into table select…完成数据插入。


为了更好的展示 and prior 的重要性和理解start with的查询机制,这次的数据添加了 company 字段,下方案例将进一步说明递归的机制。

drop table if exists tree_tb;
create table tree_tb(
	employee int,
	leader int,
	levels varchar(10),
	company varchar(10)
);

insert into tree_tb
select 1001,1005,'L1','百度' from system.dual
union all
select 1002,1005,'L1','百度' from system.dual
union all
select 1005,1006,'L2','百度' from system.dual
union all
select 1005,1008,'L2','百度' from system.dual
union all
select 1008,1010,'L3','百度' from system.dual
union all
select 1010,null,'L4','百度' from system.dual
union all
select 1001,1005,'L1','腾讯' from system.dual
union all
select 1005,1006,'L1','腾讯' from system.dual
union all
select 1005,1008,'L2','腾讯' from system.dual
union all
select 1008,1010,'L3','腾讯' from system.dual
union all
select 1010,null,'L4','腾讯' from system.dual
;

递归的执行机制:上述orcale中已明确,不再赘述

-- 不加 and prior company=company 会出现多匹多
select * from tree_tb
start with employee=1005
connect by prior leader=employee
	and prior company=company
;

结果可以看出,当添加了 and prior company=company 条件,数据可以正确提出,否则会出现数据翻倍和逻辑错误。

SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH
start with 可以增加 and 条件完成初次取数,这个条件只能加在这里,不能加在where中,因为where的条件是最后执行的

-- start with 可以加初始查询条件
select * from tree_tb
start with employee=1005 and company='百度'
connect by prior leader=employee
	and prior company=company
;

SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH
where的条件是最后执行的,用来限制最后的查询结果符合条件。

-- where 执行在最后,按照条件过滤结果数据
select * from tree_tb
where levels='L2'
start with employee=1005 and company='百度'
connect by prior leader=employee
	and prior company=company
;

SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH

四.帆软配置递归查询

帆软递归的语法实现,主要由连接的数据库决定,例如这里使用的是星环TDH,写法如下。当配置库为Postgresql时,见SQL使用技巧(3.1)递归层次查询Postgresql,更多数据库使用方法同理。
SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH
帆软前台的配置方法见下方两篇文章,不再赘述。
SQL使用技巧(3.1)递归层次查询Postgresql
FineReport帆软报表使用入门
SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH


声明:本文所载信息不保证准确性和完整性。文中所述内容和意见仅供参考,不构成实际商业建议,如有雷同纯属巧合。文章来源地址https://www.toymoban.com/news/detail-480981.html

到了这里,关于SQL使用技巧(3.2)递归层次查询Hive、Orcale和TDH的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 关于Hive的使用技巧

    前言 Hive是一个基于Hadoop的数据仓库基础架构,它提供了一种类SQL的查询语言,称为HiveQL,用于分析和处理大规模的结构化数据。 Hive的主要特点包括: 可扩展性:Hive可以处理大规模的数据,支持高性能的并行化执行。 数据抽象:Hive将数据抽象为表,可以通过HiveQL进行查询

    2024年02月14日
    浏览(38)
  • 【第33天】SQL进阶-SQL高级技巧-CTE和递归查询(SQL 小虚竹)

    回城传送–》《100天精通MYSQL从入门到就业》 今天是学习 SQL 打卡的第 33 天,每天我会提供一篇文章供群成员阅读( 不需要订阅付钱 )。 希望大家先自己思考,如果实在没有想法,再看下面

    2023年04月08日
    浏览(51)
  • Excel小技巧,使用函数(INDEX+MATCH)快速进行条件查询

    目录 Excel小技巧,使用函数(INDEX+MATCH)快速进行条件查询 1、例如:快速查找下图右边同学的总分  2、在条件查询区域,总分单元格中输入函数【=INDEX(E:E,MATCH(H2,A:A,0))】即可  3、INDEX(E:E  函数为查找结果所在列,MATCH(H2,A:A,0)函数中H2为查找值,A:A为查找所在列,0为精确匹配

    2024年02月10日
    浏览(67)
  • 【SQL Server】数据库开发指南(六)索引和视图的使用技巧、方法与综合应用

    本系列博文还在更新中,收录在专栏:#MS-SQL Server 专栏中。 本系列文章列表如下: 【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作 【SQL Server】数据库开发指南(一)数据库设计的核心概念和基本步骤 【SQL Server】数据库开发指南(二)MSSQL数据库开发对

    2024年02月06日
    浏览(72)
  • 【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项

    【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事 【SQL开发实战技巧】系列(二):简单单表查询 【SQL开发实战技巧】系列(三):SQL排序的那些事 【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列

    2024年02月21日
    浏览(43)
  • 选读SQL经典实例笔记14_层次查询

    2.6.1.1. sql 2.7.1.1. sql 3.5.1.1.  sql 4.5.1.1.  sql 5.5.1.1.  sql 6.7.1.1. Oracle Database 10g新增的CONNECT_BY_ROOT和CONNECT_BY_ISLEAF

    2024年02月15日
    浏览(105)
  • Excel 使用技巧集锦—163种技巧

    目录 一、基本方法7 1.快速选中全部工作表7 2.快速启动E 7 XCEL 3.快速删除选定区域数据 8 4.给单元格重新命名8 5.在E 中选择整个单元格范围9 XCEL 6.快速移动/复制单元格9 7.快速修改单元格式次序 9 8.彻底清除单元格内容10 9.选择单元格10 10. 为工作表命名11 11. 一次性打开多个工作

    2024年02月03日
    浏览(49)
  • sql递归查询

    一、postgresql 递归sql   sql中with xxxx as () 是对一个查询子句做别名,同时数据库会对该子句生成临时表; with recursive 则是一个递归的查询子句,他会把查询出来的结果再次代入到查询子句中继续查询 p为自定义临时表名,最后一句select后跟的字段必须小于等于t1和t2中字段。 第

    2024年02月11日
    浏览(33)
  • SQL高级:递归查询

    如果在单表或两表中存储了 树形结构 数据,那么在查询这些数据时,就有可能要用到递归查询。 在实际的业务场景中, 树形结构 的数据很常见。比如组织架构、产品材料清单、产品大类和小类等等。 递归查询也是一个很有趣的知识点。我们来学习一下它。 为了学习这个知

    2024年02月03日
    浏览(50)
  • 递归sql查询完整科目名称

    已知表 科目编号 科目名称 1001 1001 现金 1002 1002 银行存款 10020100 0100 工商银行存款 100201000001 0001 工行重庆路支行 10020200 0200 建设银行存款 100202000001 0001 建行铁北支行 需要整理成 科目编号 科目称 科目全称 1001 现金 现金 1002 银行存款 银行存款 10020100 工商银行存款 银行存款

    2024年02月08日
    浏览(40)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包