SQL查询案例:多行转换为一行(转)

摘要:
其想法是简单地查看这个结果,这与基于字符的GROUPBY处理非常相似。数字类型可以是SUM,但字符类型不能是SUM。我们必须按照MAX+MAX+MAX的顺序处理它。

SQL查询案例:多行转换为一行

使用通常的方式测试表与测试数据

CREATE TABLE TestTitle (

name   VARCHAR(10),

titleVARCHAR(10)

);

INSERT INTO TestTitle VALUES ('张三', '程序员');

INSERT INTO TestTitle VALUES ('张三', '系统管理员');

INSERT INTO TestTitle VALUES ('张三', '网络管理员');

INSERT INTO TestTitle VALUES ('李四', '项目经理');

INSERT INTO TestTitle VALUES ('李四', '系统分析员');

要求

对于测试数据,要求查询结果为:

张三程序员,系统管理员,网络管理员

李四项目经理,系统分析员

这种结构的结果。

思路

简单查看这个结果,很像对字符型的GROUP BY处理。

数值类型的可以SUM,但是字符类型的无法这么处理。

只好依次MAX(1) + MAX(2) + MAX(3)这种办法来处理。

实现

第一步,设置好分组的编号

SELECT

ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS no,

name,

title

FROM

TestTitle

ORDER BY

name,

title

no                   name       title

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

                   1李四        系统分析员

                   2李四        项目经理

                   1张三        程序员

                   2张三        网络管理员

                   3张三        系统管理员

第二步,根据有编号的子查询,进行分组处理

SELECT

name,

CASE WHEN COUNT(title) = 1 THEN MAX(title)

       WHEN COUNT(title) = 2 THEN

         MAX( CASE WHEN SubQuery.no = 1 THEN title + ',' ELSE '' END )

         + MAX( CASE WHEN SubQuery.no = 2 THEN titleELSE '' END )

       WHEN COUNT(title) = 3 THEN

         MAX( CASE WHEN SubQuery.no = 1 THEN title + ',' ELSE '' END )

         + MAX( CASE WHEN SubQuery.no = 2 THEN title + ','ELSE '' END )

         + MAX( CASE WHEN SubQuery.no = 3 THEN titleELSE '' END )

END AS new_title

FROM

(

SELECT

    ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS no,

    name,

    title

FROM

    TestTitle

) subQuery

GROUP BY

name

执行结果

name       new_title

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

李四        系统分析员,项目经理

张三        程序员,网络管理员,系统管理员

对于SQL Server 2005 以上版本使用FOR XML的方式测试表与测试数据要求

与前面的一样

思路

首先把一个用户的数据,单独的读取出来

然后按照分组进行处理

实现

第一步 把一个用户的数据,单独的读取出来

SELECT

',' + title

FROM

TestTitle

WHERE

name = '张三'

FOR XML PATH('')

第二步Group By每个人

SELECT

name,

STUFF(

   (

   SELECT

     ',' + title

   FROM

     TestTitle subTitle

   WHERE

     name = TestTitle.name

   FOR XML PATH('')

   ),

   1, 1, '') AS allTitle

FROM

TestTitle

GROUP BY

name

执行结果

name      allTitle

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

李四        项目经理,系统分析员

张三        程序员,系统管理员,网络管理员

对于SQL Server 2005 以上版本使用 CTE 的处理方式 (使用递归方式处理)

WITH
t1  AS
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS ID,
    name,
    title
  FROM
    TestTitle
),
t2 AS
(
  SELECT 
    t1.id, 
    t1.name, 
    CAST(t1.title AS varchar(100)) AS title
  FROM 
    t1 
  WHERE 
    t1.id = 1
  UNION ALL
  SELECT 
    t1.id, 
    t2.name, 
    CAST( t1.title + ',' + t2.title AS varchar(100)) AS title
  FROM 
    t1, t2
  WHERE 
    t1.name = t2.name
    AND t1.id = (t2.id + 1)
)
SELECT
  name, 
  title
FROM
  t2
WHERE
  NOT EXISTS (
    SELECT 1
    FROM t2 t22
    WHERE
      t2.name = t22.name
      AND t2.id < t22.id
  );

name       title

---------- -----------------------------------------------------------
-------------------------------
张三         系统管理员,网络管理员,程序员

李四         项目经理,系统分析员


(2 行受影响)

对于MySQL使用 GROUP_CONCAT 函数 的方式进行处理(非常简单)

mysql> SELECT
    ->   name,
    ->   GROUP_CONCAT(title) AS allTitle
    -> FROM
    ->   TestTitle
    -> GROUP BY
    ->   name;
+------+------------------------------+
| name | allTitle                     |
+------+------------------------------+
| 李四 | 项目经理,系统分析员          |
| 张三 | 程序员,系统管理员,网络管理员 |
+------+------------------------------+
2 rows in set (0.00 sec)

对于Oracle使用 WMSYS.WM_CONCAT 函数 的方式进行处理(也非常简单)

SQL> 
SQL> SELECT
  2    name,
  3    WMSYS.WM_CONCAT(title) AS allTitle
  4  FROM
  5    TestTitle
  6  GROUP BY
  7    name;

NAME
----------
ALLTITLE
-------------------------------------------
李四
项目经理,系统分析员

张三
程序员,系统管理员,网络管理员


 

对于 PostgreSQL 使用 string_agg  函数 的方式进行处理(也非常简单)

Test=#

Test=# SELECT

Test-#   name,

Test-#   string_agg(title,',') AS allTitle

Test-# FROM

Test-#   TestTitle

Test-# GROUP BY

Test-#   name;

 name |           alltitle

------+------------------------------

 李四 | 项目经理,系统分析员

 张三 | 程序员,系统管理员,网络管理员

(2 行记录)

对于 DB2 ,也是使用 CTE 递归的方式处理


WITH
t1 (id, name, title) AS
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY name ORDER BY title) AS ID,
    name,
    title
  FROM
    TestTitle
),
t2 (id, name, title) AS
(
  SELECT 
    t1.id, 
    t1.name, 
    CAST(t1.title AS varchar(100)) AS title
  FROM 
    t1 
  WHERE 
    t1.id = 1
  UNION ALL
  SELECT 
    t1.id, 
    t2.name, 
    CAST( t1.title || ',' || t2.title AS varchar(100)) AS title
  FROM 
    t1, t2
  WHERE 
    t1.name = t2.name
    AND t1.id = (t2.id + 1)
)
SELECT
  name, 
  title
FROM
  t2
WHERE
  NOT EXISTS (
    SELECT 1
    FROM t2 t22
    WHERE
      t2.name = t22.name
      AND t2.id < t22.id
  );

NAME       TITLE

---------- ---------------------------------------------------------------------
-------------------------------
SQL0347W  递归公共表表达式 "WZQ.T2" 可能包含无限循环。  SQLSTATE=01605

李四       项目经理,系统分析员

张三       网络管理员,系统管理员,程序员


  已选择 2 条记录,打印 1 条警告消息。

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

FOR XML PATH 的作用是把结果以xml文本的形式显示出来,也就是说,最终结果就是一个字符串,因此我们就不需要使用什么字符串合并函数了。

STUFF函数的原型是 Stuff(str1, start, len, str2),作用是,删掉str1中start开始的len个字符,用str2替换。因此,可以起到在多个项之间插入分隔符。

比如,

 
select ','+name from student for xml path(''group by class

输出结果可能是

,Jim,Kate,Tom,Sally

如果使用STUFF,可以删掉第一个空格

 
stuff(select ','+name from student for xml path(''group by class, 1, 1, '')
如果使用的是hibernate 就要小心,用for xml path 查出来可能会是org.hibernate.lob.SerializableClob,而不是一个字符串,我现在是使用下面的方法转换的
[java] view plain copy
 
  1. if(value != null && value instanceof org.hibernate.lob.SerializableClob){  
  2.                         org.hibernate.lob.SerializableClob clob = (org.hibernate.lob.SerializableClob)value;  
  3.                         try {  
  4.                             Reader reader = clob.getCharacterStream();  
  5.                             BufferedReader br = new BufferedReader(reader);  
  6.                             StringBuilder sb = new StringBuilder();  
  7.                             String str = null;  
  8.                             while((str = br.readLine()) != null){  
  9.                                 sb.append(str);  
  10.                             }  
  11.                             _map.put(key.toUpperCase(), sb.toString());  
  12.                         } catch (SQLException e) {  
  13.                             e.printStackTrace();  
  14.                         } catch (IOException e) {  
  15.                             e.printStackTrace();  
  16.                         }  
  17.                     }  
不知道有没有更简单的方法进行配置

免责声明:文章转载自《SQL查询案例:多行转换为一行(转)》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇iOS开启隐藏文件以及显示文件方法C#接口(interface)与类(class)的实例互相转换深度解析下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

MySQL快速回顾:计算字段与函数

9.1 计算字段 存储在数据库表中的数据一般不是应用程序所需要的格式。比如: 如果想要在一个字段中既显示公司名,又显示公式的地址,但这两个信息一般包含在不同的表列中。 城市、州和邮政编码存储在不同的列中,但邮件标签打印程序却需要把它们作为一个恰当格式的字段检索出来。 列数据是大小写混合的,但报表程序需要把所有数据按大写表示出来。 在上面举的例子中,存储...

SQL 删除前100条 with as

with cte as(select top 50* from tableName)delete from cte WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,...

Visual Studio 实用插件全集

Microsoft Visual Studio(简称VS)是美国微软公司的开发工具套件系列产品。VS是一个基本完整的开发工具集,它包括了整个软件生命周期中所需要的大部分工具,如UML工具、代码管控工具、集成开发环境等等。所写的目标代码适用于微软支持的所有平台,包括Microsoft Windows、Windows Mobile、Windows CE、.NE...

snowflake 分布式唯一ID生成器

本文来自我的github pages博客http://galengao.github.io/ 即www.gaohuirong.cn 摘要: 原文参考运维生存和开源中国上的代码整理 我的环境是python3.5,pip8.2的 一、python版本 前言 由于考虑到以后要动态切分数据,防止将不同表切分数据到同一个表中时出现主键相等的冲突情况,这里我们使用...

Kali学习笔记39:SQL手工注入(1)

终于到了SQL注入 最大的、最经典的、最常见的Web漏洞就是SQL注入漏洞 SQL注入的原理这里就不说了,百度 打开DVWA,SQL注入测试模块 测试单引号,发现出错,于是想到测试语句: 1' or '1'='1 成功: 测试是否存在漏洞: 1' and '1'='1 如果返回数据,但是1' and '1'='0 不返回数据,代表存在sql注入 或者简...

oracle11g dataguard部署指南

一、Oracle11DB+DG配置 1. 单机环境介绍(PRIMARY DATABASE)主库primary public ip 192.168.0.252instanceoracledb_name oraclestorage mode /orasjrz/crds3db/oradata /orasjrz/crds3db/oraarch 2. 单机环境介绍(...