您好,登錄后才能下訂單哦!
建一個(gè)Employee表,有兩個(gè)列,一個(gè)是name,一個(gè)是所屬于的部門(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
現(xiàn)在想寫一個(gè)SQL,把一個(gè)部門的員工給做統(tǒng)計(jì),每個(gè)部門一行
數(shù)據(jù)變成下面的樣子
DEPT NAMES
----------- ----------------
Development John
ID Mark
L3 Jack,Lee
Quality Lily
Solutions Berni,Miso,Serge
實(shí)現(xiàn)的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;
解釋幾個(gè)DB2 XML方法的含義
XMLELEMENT是把標(biāo)量轉(zhuǎn)成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把多個(gè)XML進(jìn)行聚合,這里要給出分組的列(Dept),并且每個(gè)組里,以NAME進(jìn)行排序
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轉(zhuǎn)換成為一個(gè)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
到了這個(gè)地方就很簡單了,把
干掉,把
轉(zhuǎn)化成,即可
后來,出現(xiàn)了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之后,這個(gè)問題得到了完美的解決
SELECT Dept,
LISTAGG(name, ',')
WITHIN GROUP (ORDER BY name)
FROM Employee
GROUP BY Dept;
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。