`
oham_一1一
  • 浏览: 51420 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

Oracle 连接查询,字表多行字段合并为一行,以逗号分隔的查询SQL

阅读更多

要做什么看标题就懂了。。。

 

背景:1. 现有二表:test_main, test_sub

           2. test_sub表的m_id是关联test_main的m_id字段的外键,main对sub为一对多。

            求做成以下结果:



 

步骤

1.

test_main建表SQL:

create table test_main(
       m_id number not null,
       m_name varchar(10),
       constraint test_main_pk primary key(m_id)
)

 test_sub建表SQL:

create table test_sub(
       s_id number,
       m_id number,
       s_name varchar(10),
       constraint test_sub_pk primary key(s_id),
       constraint test_sub_fk foreign key(m_id) references test_main(m_id)
)

 

2.插入数据:

test_main 

 test_sub                       

 

 

3.最终的查询SQL

select m_name, 
       ltrim(max(sys_connect_by_path(s_name, ',')), ',') as sub_names
from (
       select m_name, s_name, m_id, rn1,
                lead(rn1) over(partition by m_id order by rn1) rn2
         from (
                select core.*, row_number() over(order by core.m_id desc) rn1
                from (        
                       select m.m_name, s.m_id, s.s_name
                       from test_main m 
                       join test_sub s on m.m_id = s.m_id
                     ) core     
              )tmp
     )tmp2
start with rn2 is null
connect by rn2 = prior rn1
group by m_id, m_name

 

 

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

思路:

1. 看core 子表,是业务核心SQL,目的是查出想要的所有数据。

 

 select m.m_name, s.m_id, s.s_name
 from test_main m 
 join test_sub s on m.m_id = s.m_id

 

结果:


 

2. row_number() over( [partition by col_g]  order by col1 [, col2, ...] )函数, 这一函数表示若有partition字句,先根据col_g进行分组,在分组内根据col1 [, col2, ....]排序, 然后返回每组内部排序后的顺序编号(组内连续的唯一的),若无partition字句,表示不分组。row_number()与rownum字段的区别在于:使用rownum进行排序的时候是先对结果集加入伪行号,然后再进行排序,而row_number()是根据排序从句(order by col1...)先排序再计算行号码。 

row_number()和rownum差不多,功能更强一点,根据partition by col_g分组后,可以在各个分组内从1开始排序。 

 

如下SQL,根据m_id排个序然后给个rownumber rn1

 select core.*, row_number() over(order by core.m_id desc) rn1
 from (        
              select m.m_name, s.s_name, s.m_id
              from test_main m 
               join test_sub s on m.m_id = s.m_id
         ) core     

 结果:


 

 3.lead(col [, offset] [, default] ) over( [partition by col_g]  order by col1 [, col2, ...] ) 函数,lead为返回相对当前这行记录的下offset行记录相应字段col的值,default表示当col的值为null时返回的值,默认为null, 不指定offset表示默认为1,则意思是返回当前行记录的下一行记录col的值,若为负数,则表示相对当前行记录的后offset行(要看oracle版本,有些貌似不支持负数)。

以test_sub表为例,先看以下SQL:

select s_id, lead(s_id) over(order by s_id asc) as sid
from test_sub

 意思是查出s_id和此s_id 对应的下一行s_id的值,结果:


 

再看SQL:

select s_id, m_id, lead(s_id) over(partition by m_id order by s_id asc) as sid
from test_sub

 加入分组后,则lead操作的范围缩小到组里面了,先看结果:


 对比上面没有partition的SQL结果可以发现,s_id为3的记录在此处sid为空,那是因为partition by m_id的原因,现在是m_id组内的lead操作。在m_id为1的组里,s_id为3的记录是最后的记录了,所以它往后的不存在的行记录的s_id 的值必然为null。而在没有partition的情况下s_id为3的记录往后的行记录是s_id 为4的那个,所以lead出来是4

 

回到tmp2子查询语句上面,SQL:

 select m_name, s_name, m_id, rn1,
        lead(rn1) over(partition by m_id order by rn1) rn2
 from (
          select core.*, row_number() over(order by core.m_id desc) rn1
          from (        
                   select m.m_name, s.s_name, s.m_id
                   from test_main m 
                   join test_sub s on m.m_id = s.m_id
                ) core     
       )tmp

  tmp子查询语句先根据m_id分组后表上行号RN1,然后根据RN1进行m_id组内lead操作。


 结果:

 

4.start with...connect by 字句。。。

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。

在此另举以案例说明之:

   1)创建示例表

CREATE TABLE TBL_TEST
(
  ID    NUMBER,
  NAME  VARCHAR2(100 BYTE),
  PID   NUMBER                                  DEFAULT 0
);

 

2)插入数据如下:

 

从上面的记录中id与pid的关系可以得出此时所有记录可以抽象出一棵树,其中PID字段表示父节点的ID值:



 3)测试SQL:

 

select * from TBL_TEST
 start with id=1
 connect by prior id = pid
 意思是从id为1的节点开始遍历其所有子节点,结果:

 

从结果看,这是个先序遍历(有关二叉树且看在下所作笔记:http://gwoham-163-com.iteye.com/blog/1896260),或自行google。至于oracle底层如何执行,恕在下愚钝。。。

再看start with 是指定从那个节点开始遍历;注意此处connect by prior id = pid,prior为优先之意,此处为id优先,则整个SQL表示遍历id为1节点的所有子节点。若改为connect by prior pid = id则变成遍历其所有父节点了。

再看SQL:

 select * from TBL_TEST
 start with id=5
 connect by prior pid = id

 意思是遍历id为5的节点的所有父节点(不包括最root节点,应为id为0的记录没有),结果:

 

 

 了解了start with...connect by子句是以遍历树的方式查询出记录,分析tmp2查询子句结果集:

tmp2查询子句返回具有树形结构的数据,RN2为child,RN1为parent。

此查询语句表示查询子节点RN2为空的所有父节点,目的是揪出根据m_Id分组的三棵树的数据:

select m_name, rn1, rn2
   
from (
       select m_name, s_name, m_id, rn1,
              lead(rn1) over(partition by m_id order by rn1) rn2
        from (
                select core.*, row_number() over(order by core.m_id desc) rn1
                from (        
                       select m.m_name, s.s_name, s.m_id
                       from test_main m 
                       join test_sub s on m.m_id = s.m_id
                     ) core     
              )tmp
     )tmp2
start with rn2 is null
connect by rn2 = prior rn1

 

给出tmp2的数据的树状图:

 
 三棵树分别是Oham,Lulu,Cancan ;红色部分为root节点,实际不存在的记录。

 

三棵树分别遍历,结果为:

但问题是为什么要遍历这样的树呢?目的是使用sys_connect_by_path(node_col, conn_sbl)函数,其意思是让你把节点字段node_col按照当前遍历路径用conn_sbl符号连接起来,建议自行google深入了解。

执行SQL:

select m_name, rn1, rn2, s_name,
       sys_connect_by_path(s_name, ',') as sub_names
from (
       select m_name, s_name, m_id, rn1,
                lead(rn1) over(partition by m_id order by rn1) rn2
         from (
                select core.*, row_number() over(order by core.m_id desc) rn1
                from (        
                       select m.m_name, s.m_id, s.s_name
                       from test_main m 
                       join test_sub s on m.m_id = s.m_id
                     ) core     
              )tmp
     )tmp2
start with rn2 is null
connect by rn2 = prior rn1

 因为当前prior指定是RN1优先(遍历父节点),而sys_connect_by_path函数就把当前节点其下的子节点以及自己(从最下至上)用逗号拼接在一起(说实话还不太明白按当前路径之说法什么意思,若把优先改成RN2,遍历子节点,那sys_connect_by_path就把其上的父节点以及自己从最上至下拼接),结果:


 
 接下来就简单了,就上面的结果简单整理一下就行了,按m_name分组,取sub_names值最长的记录,把左边的逗号trim掉,然后大功告成。

执行SQL:

select m_name, 
      ltrim(max (sys_connect_by_path(s_name, ',')), ',') as sub_names
from (
       select m_name, s_name, m_id, rn1,
                lead(rn1) over(partition by m_id order by rn1) rn2
         from (
                select core.*, row_number() over(order by core.m_id desc) rn1
                from (        
                       select m.m_name, s.m_id, s.s_name
                       from test_main m 
                       join test_sub s on m.m_id = s.m_id
                     ) core     
              )tmp
     )tmp2
start with rn2 is null
connect by rn2 = prior rn1
group by m_name

 

 


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

整理思路一下:

                     1.core是查出所有想要的数据。

                     2.tmp是对core的数据加入行序号。

                     3.tmp2是利用tmp中的行序号作分组lead操作,目的是构造出具有父子关系(树状)的数据形式 ——RN1为父,RN2为子,对应回test_main与test_sub的一对多主从关系。

                     4.有了tmp2具有树状结构的数据记录,结合start with...connect by语句,运用sys_connect_by_path函数查询出拼接出逗号的字段值。

          5.最后对结果进行分组修饰处理。

 

 

 

 


 

  • 大小: 1.1 KB
  • 大小: 2 KB
  • 大小: 1.3 KB
  • 大小: 1.9 KB
  • 大小: 2.4 KB
  • 大小: 1.3 KB
  • 大小: 1.5 KB
  • 大小: 2.9 KB
  • 大小: 1.4 KB
  • 大小: 1.3 KB
  • 大小: 1.2 KB
  • 大小: 4.5 KB
  • 大小: 2 KB
  • 大小: 5.5 KB
  • 大小: 3.5 KB
分享到:
评论

相关推荐

    Oracle字段转字符串/多行记录合并/连接/聚合字符串的几种方法

    在Oracle数据库中,有时我们需要将多个字段值转换为单个字符串,或者将多行记录合并成一行,这在数据处理和报告生成时尤其常见。Oracle提供了多种方法来实现这一目标,包括使用内置函数、PL/SQL过程以及一些高级特性...

    oracle sql 某列的值按照逗号分割显示成多行.sql

    oracle 某列存儲的值是有逗號的字符串,希望通過逗號分隔開來顯示多行。sql,有預期結果,有項目實戰。

    oracle某个字段多行记录转化为一行

    根据提供的信息,本文将详细介绍如何在Oracle数据库中将某个字段的多行记录转换为一行记录的方法。这种方法在处理大量数据时非常有用,特别是在需要将多个值汇总到一个字段中的场景下。 ### 标题理解:“oracle某个...

    oracle将以逗号分隔字符串转多行

    在Oracle数据库中,将逗号分隔的字符串转换为多行是常见的数据处理需求,尤其在需要对每个分隔项进行单独操作时。这个过程通常涉及到字符串处理函数,如`REGEXP_SUBSTR`和`REPLACE`,以及层次查询结构`CONNECT BY`。...

    SQL 语句 将一个表中用特殊字符分割的字段转换成多行数据.docx

    接下来,我们创建一个示例表 `db_TestTemp`,其中包含一个字段 `Engineer`,类型为 `NVARCHAR(MAX)`,用于存储需要拆分的字符串。 ```sql CREATE TABLE db_TestTemp ( Engineer NVARCHAR(MAX), BeginDate DATE...

    oracle实现行转列功能,并使用逗号进行隔开拼接,成为一条数据.pdf

    Oracle 中实现行转列功能,并使用逗号进行隔开拼接,成为一条数据是指将多行数据合并成一行数据,并用逗号分隔每个字段的值。这种功能在实际应用中非常有用,例如在报表生成、数据分析和数据整合等场景中。 在 ...

    oracle多行合并一行

    ### Oracle多行合并为一行的方法 在Oracle数据库中,有时候我们需要将多行数据合并成一行进行展示或处理,尤其是在报表生成、数据展示等场景中。本文将详细介绍如何使用Oracle纯SQL语句实现这一功能,并通过具体...

    Oracle字段根据逗号分割查询数据的方法

    在Oracle数据库中,有时我们遇到一个特殊的查询需求,即字段中的值是以逗号分隔的字符串,我们需要根据每个单独的值来查询数据。在这种情况下,简单的`LIKE`查询无法满足需求,因为`LIKE`通常用于匹配模式,而不是...

    sql里将重复行数据合并为一行数据使用逗号进行分隔

    两种方法都能达到预期的效果,将相同`CityName`的行合并,并以逗号分隔`UserName`。在实际应用中,可以根据数据规模和性能需求选择合适的方法。 此外,`FOR XML PATH` 是SQL Server中一个强大的功能,它可以将查询...

    oracle 存储过程中某入参是逗号分隔,并且参数要使用在in过滤语句中

    oracle存储过程中入参是逗号分隔,并且参数要使用在in过滤语句中查询数据。处理的方法与实现

    oracle 关于字符串中带有逗号间隔的查询.doc

    在Oracle数据库中,进行字符串查询时,特别是当字符串中包含逗号等特殊字符时,我们需要巧妙地使用SQL的LIKE操作符来实现精确匹配。这里,我们面对的问题是想要查询DWDH字段中含有"飞信"但不包含"移动飞信"或"飞信...

    取得oracle中某表的所有字段名

    对于获取所有字段名并以逗号分隔,可以使用如下的SQL语句: ```sql SELECT MAX(substr(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','), 2)) AS col FROM ( SELECT COLUMN_NAME, column_id FROM user_tab_columns WHERE ...

    MySQL将一个字段中以逗号分隔的取出来形成新的字段实现

    在MySQL数据库中,经常遇到需要处理以逗号分隔的字段值的情况,这些字段通常用于存储多值数据,比如在本例中,`related_shop_ids`字段存储了多个商店ID,用逗号分隔。本篇文章将详细讲解如何将这样的字段拆分成多个...

    一个oracle根据特定字符截取数据

    假设我们有一个名为 `data_column` 的列,其中包含以逗号分隔的数据,如 'value1,value2,value3',我们可以使用以下SQL语句来获取第一个值: ```sql SELECT SUBSTR(data_column, 1, INSTR(data_column, ',', 1) -...

    SQL,条件加逗号神器

    可以快速将字符串转化为大小写、加引号和逗号便于查询 可以快速将字符串转化为大小写、加引号和逗号便于查询

    Oracle逗号分隔列转行实现方法

    那么,现在如果给出一个权限编号,要去检索出用后这个权限的用户集合,就会需要在逗号分隔的多个权限编号中去匹配给出的这个权限编号。如果使用like去做,一则效率不高,二则匹配不精确。因此用到了逗号分隔列转行的...

    Sql function 多行中的列合并为一行一列的方法

    总结来说,这个示例展示了一种使用SQL自定义函数将多行数据合并为一行的方法,这在处理多值字段时非常有用,但需要注意性能和数据长度限制。在实际应用中,根据数据库系统的特性选择最适合的解决方案是至关重要的。

    PL/SQL中查询Oracle大数(17位以上)时显示科学计数法的解决方法

    特别是在Oracle数据库中,当涉及到17位以上的数字时,可能会出现一个问题:这些大数在查询结果中会以科学计数法的形式显示,而不是常规的数字格式。这不仅会影响数据的可读性,也可能导致后续的数据处理出现错误。...

Global site tag (gtag.js) - Google Analytics