Oracle SQL:纯SQL实现多行合并为一行

需积分: 50 54 下载量 42 浏览量 更新于2024-09-11 2 收藏 38KB DOC 举报
"SQL实现将多行数据合并成一行,主要针对Oracle数据库,通过使用SQL查询和特定函数来完成这一操作。" 在SQL数据库查询中,有时我们需要将多行数据合并为一行,尤其是在数据聚合或者展示分组信息时。这里以Oracle数据库为例,介绍一种纯SQL方法来实现这个功能,无需创建额外的自定义函数。问题背景是在一个存储股票与研究员对应关系的表中,每个股票可能有多个研究员,需要将同一股票的所有研究员代码用逗号分隔显示在一行。 首先,我们来看表`m_researcher_stock_rel`的结构,它包含两个字段:`N_SEC_CODE`(股票代码)和`C_RESEARCHER_CODE`(研究员代码)。目标是将每个股票代码对应的所有研究员代码合并,并用逗号分隔。 传统的解决办法可能涉及编写自定义函数,或者受限于合并的列数。但是,通过Oracle的层次查询和`SYS_CONNECT_BY_PATH`函数,我们可以巧妙地一次性解决这个问题。层次查询用于创建一个递归结构,`SYS_CONNECT_BY_PATH`则可以将每一行的数据按照指定的路径连接起来。 下面是实现步骤: 1. 使用`ROW_NUMBER()`窗口函数为每个股票代码及其研究员代码分配行号,按股票代码排序。 2. 使用`LEVEL`字段表示当前行在递归树中的深度,这将帮助我们构建路径。 3. `SYS_CONNECT_BY_PATH`函数将研究员代码连接起来,中间用斜线`/`分隔,形成路径字符串。 4. 最后,通过`TRANSLATE()`函数去除首字符的斜线,并将斜线替换为逗号,得到最终的逗号分隔列表。 具体SQL查询语句如下: ```sql SELECT n_sec_code, TRANSLATE(LTRIM(text, '/'), '/*', ',*') AS researcherList FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY n_sec_code ORDER BY n_sec_code, lvl DESC) AS rn, n_sec_code, text FROM ( SELECT n_sec_code, LEVEL AS lvl, SYS_CONNECT_BY_PATH(c_researcher_code, '/') AS text FROM ( SELECT n_sec_code, c_researcher_code, ROW_NUMBER() OVER (PARTITION BY n_sec_code ORDER BY n_sec_code, c_researcher_code) AS x FROM m_researcher_stock_rel ORDER BY n_sec_code, c_researcher_code ) a CONNECT BY PRIOR n_sec_code = n_sec_code AND PRIOR x + 1 = x START WITH x = 1 ) WHERE rn = 1 ) ``` 这个查询首先按照股票代码和研究员代码的顺序生成路径,然后选取每个股票的第一行(`rn = 1`),因为层级最高的行包含了所有研究员代码的完整路径。`LTRIM`函数去除首位的斜线,`TRANSLATE`函数将剩余的斜线替换为逗号,从而得到预期的逗号分隔研究员列表。 通过这种方式,你可以方便地处理任意数量的列,而不会受到列数限制,也不需要在数据库中创建额外的函数,实现了一种高效且灵活的多行合并方法。