表结构和数据如下(表名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,去掉最前面的那个分号,完成。
分享到:
相关推荐
在本文中,我们将介绍如何在 SQL Server 中将一个字段的多个记录值合并到一行显示,并提供相关的示例代码和实现方法。 知识点 1:使用 User-Defined Function(UDF)实现字段值合并 在 SQL Server 中,我们可以...
SQL 数据表列转换为一行 在数据库管理中,经常会遇到将...这个示例展示了如何使用存储过程、临时表、动态 SQL 语句、 WHILE 循环、CALL 语句、ALTER TABLE 语句和 UPDATE 语句来将 SQL 数据表的一列转换为一行多列。
* SELECT 语句:从数据库表中检索数据行和列 * INSERT 语句:向数据库表添加新数据行 * DELETE 语句:从数据库表中删除数据行 * UPDATE 语句:更新数据库表中的数据 二、数据定义 * CREATE TABLE 语句:创建一个...
### 使用单条SQL语句更新数据表中的某一列至另一表 #### 背景介绍 在数据库管理中,经常需要对数据进行更新操作来确保数据的准确性和时效性。有时候,这种更新不仅限于同一张表内,还可能涉及到跨表的操作。例如,...
本文将深入探讨如何利用SQL语句实现对某一列所有字段中的特定字符进行替换的操作,这一过程通常用于数据清洗、格式调整或错误修正等场景。 ### SQL语句替换某列所有字段中的某个字符 #### 1. 基本概念与应用场景 ...
* SELECT 语句:从数据库表中检索数据行和列。 * INSERT 语句:向数据库表添加新数据行。 * DELETE 语句:从数据库表中删除数据行。 * UPDATE 语句:更新数据库表中的数据。 数据概念 * CREATE TABLE 语句:创建一...
- 子查询:一个查询嵌套在另一个查询中,可以用于比较、过滤或者计算。 - 视图:视图是虚拟的表,基于一个或多个表的查询结果。它们提供了一种简化复杂查询和保护数据的方式。 - 分页查询:通过LIMIT和OFFSET...
- **EXCEPT**:返回在第一个SELECT语句中出现但不在第二个SELECT语句中出现的行。 6. **视图(View)** - 视图是基于一个或多个表的虚拟表,它不存储数据,而是提供一个预定义的查询结果集。 - 创建视图的语句:...
### SQL列数不固定的查询语句解析与应用 #### 核心知识点概述 在SQL查询中,有时会遇到数据表的列数不固定的情况,即数据表中的某些列可能存在或缺失,这通常发生在需要根据不同的条件对数据进行聚合或者分组的...
例如在usertable和citytable表中同时存在cityid列,在查询两个表中的cityid时应使用下面语句格式加以限定: 代码:SELECT `username`,citytable.cityidFROM `usertable`,`citytable`WHERE usertable.cityid=...
4. **CREATE VIEW** - 创建视图,即虚拟表,基于一个或多个表的查询结果。 5. **DROP VIEW** - 删除视图。 6. **CREATE INDEX** - 为表创建索引,提高查询速度。 7. **DROP INDEX** - 删除已有的索引。 8. **CREATE ...
例如,原始表可能按照行存储了不同类别的数据(如物品名和颜色),现在我们需要将其转换为每个类别单独成为一列的形式,便于后续的数据分析或展示。 #### 二、具体实现步骤 首先,根据提供的示例代码,我们创建了...
例如,要在Store_Information表格中找出所有不同的店名,可以使用以下SQL语句: SELECT DISTINCT store_name FROM Store_Information ### WHERE指令 WHERE指令让我们能够选择性地抓取资料。WHERE指令的语法结构...
SQL 基本语句大全 SQL(Structured Query Language)是一种专门用于管理关系数据库管理系统(RDBMS)的语言。它是一种标准语言,用于存储、操作和检索数据库中的数据。 数据操作 * `SELECT`:从数据库表中检索...
- **SELECT语句**:用于从一个或多个表中检索数据。例如,`SELECT * FROM 表名`会返回表中的所有记录,`SELECT 列名1, 列名2 FROM 表名`则只返回指定列的记录。 - **WHERE子句**:用于设定查询条件。例如,`SELECT...
用户可以用INSERT语句将一行记录插入到指定的一个表中。例如,要将雇员John Smith的记录插入到本例的表中,可以使用如下语句: INSERT INTO EMPLOYEES VALUES ('Smith','John','1980-06-10', 'Los Angles' ,...
联合查询(`UNION`)允许将两个或多个`SELECT`语句的结果集合并为一个结果集。默认情况下,`UNION`会自动去除重复的行。例如,要合并两个查询的结果: ```sql SELECT column_name(s) FROM table1 UNION SELECT ...
### ArcGIS中的SQL语句详解 #### 一、SQL在ArcGIS中的角色与应用 SQL,全称为Structured Query Language,即结构化查询语言,是专为数据库设计的一种标准语言,用于管理和操作数据库中的数据。在ArcGIS环境中,SQL...