使用通常的方式
测试表与测试数据
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
-------------------------------------------
李四
项目经理,系统分析员
张三
程序员,系统管理员,网络管理员
对于 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 条警告消息。
http://hi.baidu.com/wangzhiqing999/blog/item/a57d1a86009e439ff703a67a.html
分享到:
相关推荐
11.8.1 将结果集转置为一行 11.8.2 把结果集转置为多行 11.9 递归查询 11.9.1 Oracle中的CONNECT BY子句 11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用...
11.8.1 将结果集转置为一行 11.8.2 把结果集转置为多行 11.9 递归查询 11.9.1 Oracle中的CONNECT BY子句 11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用...
11.8.1 将结果集转置为一行 11.8.2 把结果集转置为多行 11.9 递归查询 11.9.1 Oracle中的CONNECT BY子句 11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用...
11.8.1 将结果集转置为一行 11.8.2 把结果集转置为多行 11.9 递归查询 11.9.1 Oracle中的CONNECT BY子句 11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用...
11.8.1 将结果集转置为一行 11.8.2 把结果集转置为多行 11.9 递归查询 11.9.1 Oracle中的CONNECT BY子句 11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用...
XMLToDataSet 将XML文件转换为数据集的案例。 XmlWriterSample XML编写器的应用案例。 <br>第21章(\C21) 示例描述:介绍如何使用缓存。 CacheDependency 缓存依赖实例 ...
实例026 使用as关键字将对象转换为指定类型 32 2.4 流程控制语句的使用 34 实例027 小明去学校和医院分别要走哪条路 34 实例028 利用条件语句判断用户登录身份 35 实例029 判断指定月份属于哪个季节 36 实例030 使用...
37 2.2.3 隐含转换 37 2.3 标识符的命名 38 2.3.1 标识符的命名规则 38 2.3.2 代码演示如何定义标识符 38 2.3.3 不好的标识符命名 38 2.3.4 良好的标识符命名 39 2.4 关键字 39 2.5 代码注释 40 2.5.1 行注释 40 ...
13.19 将日期转换为字符串的方法 13.20 检测是否是闰年 13.21 年份加减函数 13.22 精确到千分之一秒 13.23 离开某天的时间 13.24 判断两个字符串日期的大小 13.25 显示登录时间 13.26 中文日期样式(一) 13.27 中文...
13.19 将日期转换为字符串的方法 13.20 检测是否是闰年 13.21 年份加减函数 13.22 精确到千分之一秒 13.23 离开某天的时间 13.24 判断两个字符串日期的大小 13.25 显示登录时间 13.26 中文日期样式(一) 13.27 中文...