建一个Employee表，有两个列，一个是name，一个是所属于的部门(dept)
CREATE TABLE Employee(name VARCHAR(15),dept VARCHAR(15));
insert into Employee values('Jack','L3');
insert into Employee values('Lily','Quality');
insert into Employee values('Mark','ID');
insert into Employee values('Lee','L3');
insert into Employee values('Serge','Solutions');
insert into Employee values('John','Development');
insert into Employee values('Miso','Solutions');
insert into Employee values('Berni','Solutions');
select * from Employee;
NAME DEPT
----- -----------
Jack L3
Lily Quality
Mark ID
Lee L3
Serge Solutions
John Development
Miso Solutions
Berni Solutions
现在想写一个SQL，把一个部门的员工给做统计，每个部门一行
数据变成下面的样子
DEPT NAMES
----------- ----------------
Development John
ID Mark
L3 Jack,Lee
Quality Lily
Solutions Berni,Miso,Serge
实现的SQL
SELECT Dept
,SUBSTR(Names, 1, LENGTH(names) - 1)
FROM (
SELECT Dept
,REPLACE(REPLACE(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME a, NAME) ORDER BY NAME) AS VARCHAR(60)), '
', ''), '
', ',') AS Names
FROM Employee
GROUP BY Dept
) AS X;
解释几个DB2 XML方法的含义
XMLELEMENT是把标量转成XML的格式
select Dept,XMLELEMENT(NAME a, NAME) from Employee;
DEPT 2
----------- ------------
L3
Jack
Quality
Lily
ID
Mark
L3
Lee
Solutions
Serge
Development
John
Solutions
Miso
Solutions
Berni
XMLAGG把多个XML进行聚合，这里要给出分组的列(Dept)，并且每个组里，以NAME进行排序
select Dept,XMLAGG(XMLELEMENT(NAME a, NAME) ORDER BY NAME) from Employee GROUP BY Dept;
DEPT 2
----------- -----------------------------------
Development
John
ID
Mark
L3
Jack
Lee
Quality
Lily
Solutions
Berni
Miso
Serge
XMLSERIALIZE()的作用是把XML转换成为一个String类型
select Dept,XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME a, NAME) ORDER BY NAME) AS VARCHAR(60)) from Employee GROUP BY Dept;
DEPT 2
----------- -----------------------------------
Development
John
ID
Mark
L3
Jack
Lee
Quality
Lily
Solutions
Berni
Miso
Serge
到了这个地方就很简单了，把
干掉，把
转化成,即可
后来，出现了XMLGROUP，使用起来也比较方便
SELECT Dept
,XMLGROUP(',' || NAME AS a ORDER BY NAME)
FROM Employee
GROUP BY Dept
DEPT 2
----------- ----------------------------------------------------------------------------------------
Development
,John
ID
,Mark
L3
,Jack
,Lee
Quality
,Lily
Solutions
,Berni
,Miso
,Serge
SELECT Dept
,XMLCAST(XMLGROUP(',' || NAME AS a ORDER BY NAME) AS VARCHAR(60))
FROM Employee
GROUP BY Dept
DEPT 2
----------- -----------------
Development ,John
ID ,Mark
L3 ,Jack,Lee
Quality ,Lily
Solutions ,Berni,Miso,Serge
SELECT Dept
,SUBSTR(XMLCAST(XMLGROUP(',' || NAME AS a ORDER BY NAME) AS VARCHAR(60)), 2) AS Names
FROM Employee
GROUP BY Dept
DEPT NAMES
----------- ----------------
Development John
ID Mark
L3 Jack,Lee
Quality Lily
Solutions Berni,Miso,Serge
到了DB2 9.7.4之后，这个问题得到了完美的解决
SELECT Dept,
LISTAGG(name, ',')
WITHIN GROUP (ORDER BY name)
FROM Employee
GROUP BY Dept;
专业从事做网站、成都做网站,高端网站制作设计,小程序设计,网站推广的成都做网站的公司。优秀技术团队竭力真诚服务，采用成都h5网站建设+CSS3前端渲染技术，响应式网站设计，让网站在手机、平板、PC、微信下都能呈现。建站过程建立专项小组，与您实时在线互动，随时提供解决方案，畅聊想法和感受。