`
zht1933
  • 浏览: 223446 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

SQL查询语句:将一列中多个不同的值放入一行的一个单元格里

阅读更多

表结构和数据如下(表名Test):

NO VALUE  NAME
1       a       测试1
1       b       测试2
1       c       测试3
1       d       测试4
2       e       测试5
4       f       测试6
4       g       测试7

Sql语句:
select No,
       ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value,
       ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name
  from (select No,
               Value,
               Name,
               rnFirst,
               lead(rnFirst) over(partition by No order by rnFirst) rnNext
          from (select a.No,
                       a.Value,
                       a.Name,
                       row_number() over(order by a.No, a.Value desc) rnFirst
                  from Test a) tmpTable1) tmpTable2
 start with rnNext is null
connect by rnNext = prior rnFirst
 group by No;

检索结果如下:

NO VALUE    NAME
1    a;b;c;d   测试1;测试2;测试3;测试4
2    e            测试5
4    f;g          测试6;测试7

简单解释一下那个Sql吧:
1、最内层的Sql(即表tmpTable1),按No和Value排序,并列出行号:
select a.No,
       a.Value,
       a.Name,
       row_number() over(order by a.No, a.Value desc) rnFirst
  from Test a

该语句结果如下:
NO VALUE NAME RNFIRST
1     d       测试4     1
1     c       测试3     2
1     b       测试2     3
1     a       测试1     4
2     e       测试5     5
4     g       测试7     6
4     f       测试6     7


2、外层的Sql(即表tmpTable2),根据No分区,取出当前行对应的下一条记录的行号字段:
select No,
       Value,
       Name,
       rnFirst,
       lead(rnFirst) over(partition by No order by rnFirst) rnNext
  from (这里是tmpTable1的SQL) tmpTable1

lead(rnFirst):取得下一行记录的rnFirst字段
over(partition by No order by rnFirst) 按rnFirst排序,并按No分区,分区就是如果下一行的No字段与当前行的No字段不相等时,不取下一行记录显示
该语句结果如下:
NO VALUE NAME RNFIRST RNNEXT
1     d        测试4     1         2
1     c        测试3     2         3
1     b        测试2     3         4
1     a        测试1     4         NULL
2     e        测试5     5         NULL
4     g        测试7     6         7
4     f        测试6     7         NULL


3、最后就是最外层的sys_connect_by_path函数与start递归了
sys_connect_by_path(Value, ';')
start with rnNext is null
connect by rnNext = prior rnFirst

这个大概意思就是从rnNext为null的那条记录开始,递归查找,
如果前一记录的rnFirst字段等于当前记录的rnNext字段,就把2条记录的Value用分号连接起来,
大家可以先试试下面这个没有Max和Group的Sql:
select No,
       sys_connect_by_path(Value, ';') as Value,
       sys_connect_by_path(Name, ';') as Name
  from (select No,
               Value,
               Name,
               rnFirst,
               lead(rnFirst) over(partition by No order by rnFirst) rnNext
          from (select a.No,
                       a.Value,
                       a.Name,
                       row_number() over(order by a.No, a.Value desc) rnFirst
                  from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst

结果是:
NO VALUE  NAME
1 ;a  ;测试1
1 ;a;b  ;测试1;测试2
1 ;a;b;c  ;测试1;测试2;测试3
1 ;a;b;c;d ;测试1;测试2;测试3;测试4
2 ;e  ;测试5
4 ;f  ;测试6
4 ;f;g  ;测试6;测试7

可以看到,每个No的最后一条记录就是我们要的了
所以在sys_connect_by_path外面套一个Max,再加个Group by No,得到的结果就是行转列的结果了
最后再加一个Ltrim,去掉最前面的那个分号,完成。

分享到:
评论
1 楼 aguai0 2012-06-05  
想用一下,但是有点看不懂

相关推荐

    SQLServer中如何将一个字段的多个记录值合在一行显示

    在本文中,我们将介绍如何在 SQL Server 中将一个字段的多个记录值合并到一行显示,并提供相关的示例代码和实现方法。 知识点 1:使用 User-Defined Function(UDF)实现字段值合并 在 SQL Server 中,我们可以...

    将SQL数据表的一列转换为一行.docx

    SQL 数据表列转换为一行 在数据库管理中,经常会遇到将...这个示例展示了如何使用存储过程、临时表、动态 SQL 语句、 WHILE 循环、CALL 语句、ALTER TABLE 语句和 UPDATE 语句来将 SQL 数据表的一列转换为一行多列。

    SQLserver常用语句大全

    * SELECT 语句:从数据库表中检索数据行和列 * INSERT 语句:向数据库表添加新数据行 * DELETE 语句:从数据库表中删除数据行 * UPDATE 语句:更新数据库表中的数据 二、数据定义 * CREATE TABLE 语句:创建一个...

    用一条SQL语句将数据表中某列更新到另一个数据表里

    ### 使用单条SQL语句更新数据表中的某一列至另一表 #### 背景介绍 在数据库管理中,经常需要对数据进行更新操作来确保数据的准确性和时效性。有时候,这种更新不仅限于同一张表内,还可能涉及到跨表的操作。例如,...

    sql语句替换某列所有字段中的某个字符(如替换‘1,2,3,4’中的4)

    本文将深入探讨如何利用SQL语句实现对某一列所有字段中的特定字符进行替换的操作,这一过程通常用于数据清洗、格式调整或错误修正等场景。 ### SQL语句替换某列所有字段中的某个字符 #### 1. 基本概念与应用场景 ...

    学习SQL语句之SQL语句大全.pdf

    * SELECT 语句:从数据库表中检索数据行和列。 * INSERT 语句:向数据库表添加新数据行。 * DELETE 语句:从数据库表中删除数据行。 * UPDATE 语句:更新数据库表中的数据。 数据概念 * CREATE TABLE 语句:创建一...

    SQL常用查询语句大全

    - 子查询:一个查询嵌套在另一个查询中,可以用于比较、过滤或者计算。 - 视图:视图是虚拟的表,基于一个或多个表的查询结果。它们提供了一种简化复杂查询和保护数据的方式。 - 分页查询:通过LIMIT和OFFSET...

    SQL查询语句大全SQL查询语

    - **EXCEPT**:返回在第一个SELECT语句中出现但不在第二个SELECT语句中出现的行。 6. **视图(View)** - 视图是基于一个或多个表的虚拟表,它不存储数据,而是提供一个预定义的查询结果集。 - 创建视图的语句:...

    sql列数不固定查询语句

    ### SQL列数不固定的查询语句解析与应用 #### 核心知识点概述 在SQL查询中,有时会遇到数据表的列数不固定的情况,即数据表中的某些列可能存在或缺失,这通常发生在需要根据不同的条件对数据进行聚合或者分组的...

    sql查询语句详细实例教程

    例如在usertable和citytable表中同时存在cityid列,在查询两个表中的cityid时应使用下面语句格式加以限定: 代码:SELECT `username`,citytable.cityidFROM `usertable`,`citytable`WHERE usertable.cityid=...

    SQL语句英文翻译成中文

    4. **CREATE VIEW** - 创建视图,即虚拟表,基于一个或多个表的查询结果。 5. **DROP VIEW** - 删除视图。 6. **CREATE INDEX** - 为表创建索引,提高查询速度。 7. **DROP INDEX** - 删除已有的索引。 8. **CREATE ...

    Sql语句实现表的行列转换,行转列,列转行

    例如,原始表可能按照行存储了不同类别的数据(如物品名和颜色),现在我们需要将其转换为每个类别单独成为一列的形式,便于后续的数据分析或展示。 #### 二、具体实现步骤 首先,根据提供的示例代码,我们创建了...

    SQL语句基础教程

    例如,要在Store_Information表格中找出所有不同的店名,可以使用以下SQL语句: SELECT DISTINCT store_name FROM Store_Information ### WHERE指令 WHERE指令让我们能够选择性地抓取资料。WHERE指令的语法结构...

    常用sql基本语句大全

    SQL 基本语句大全 SQL(Structured Query Language)是一种专门用于管理关系数据库管理系统(RDBMS)的语言。它是一种标准语言,用于存储、操作和检索数据库中的数据。 数据操作 * `SELECT`:从数据库表中检索...

    ACCESS数据库与SQL查询语句生成示例

    - **SELECT语句**:用于从一个或多个表中检索数据。例如,`SELECT * FROM 表名`会返回表中的所有记录,`SELECT 列名1, 列名2 FROM 表名`则只返回指定列的记录。 - **WHERE子句**:用于设定查询条件。例如,`SELECT...

    数据库原理SQL语句

     用户可以用INSERT语句将一行记录插入到指定的一个表中。例如,要将雇员John Smith的记录插入到本例的表中,可以使用如下语句:  INSERT INTO EMPLOYEES VALUES  ('Smith','John','1980-06-10',  'Los Angles' ,...

    SQL查询语句大全集锦(超经典)

    联合查询(`UNION`)允许将两个或多个`SELECT`语句的结果集合并为一个结果集。默认情况下,`UNION`会自动去除重复的行。例如,要合并两个查询的结果: ```sql SELECT column_name(s) FROM table1 UNION SELECT ...

    arcgis中的sql语句

    ### ArcGIS中的SQL语句详解 #### 一、SQL在ArcGIS中的角色与应用 SQL,全称为Structured Query Language,即结构化查询语言,是专为数据库设计的一种标准语言,用于管理和操作数据库中的数据。在ArcGIS环境中,SQL...

Global site tag (gtag.js) - Google Analytics