mysql 多行子查询转一行 sql查询多行变一行

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，而不是一个字符串，我现在是使用下面的方法转换的

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. new BufferedReader(reader);  
6. new StringBuilder();  
7. 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.                     }