DB2 SQL之行合并(连接)

最新推荐文章于 2023-11-21 16:56:34 发布

cmljts0270

最新推荐文章于 2023-11-21 16:56:34 发布

阅读量843

点赞数 2

建一个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;