DB2 Distinct + xmlagg查询
问题描述:
我希望等同于DB2中MySql的GROUP_CONCAT功能。DB2 Distinct + xmlagg查询
我已经尝试过使用DB2的XML Aggrigate functon来配置相关的配置。
SELECT a.ID,
substr(xmlserialize(xmlagg(xmltext(concat(',', SPECIALISATION)))as varchar(1024)),2),
substr(xmlserialize(xmlagg(xmltext(concat(',,, BASIC_SKILL2)))as varchar(1024)),2),
substr(xmlserialize(xmlagg(xmltext(concat(',', BASIC_SKILL1)))as varchar(1024)),2)
FROM candidate_resume_data a,candidate_skills_info b,skill_special_master c,skill_master_basic2 d,skill_master_basic1 e
WHERE e.SKILL_BASIC1_ID = d.SKILL_BASIC1_ID
AND b.ID = a.ID
AND d.SKILL_BASIC2_ID = c.SKILL_BASIC2_ID
AND b.CANDIDATE_SPECIALISATION_ID = c.SKILL_SPECIAL_ID
GROUP BY a.ID;
这使我产生
ID | SPECIALISATION | BASIC_SKILL2 | BASIC_SKILL1 |
----+---------------------------------------------------------------------+
1 | Java,C++ | Development,Development | Software,Software |
但我想BASIC_SKILL2,BASIC_SKILL1的不同/独特的价值。
ID | SPECIALISATION | BASIC_SKILL2 | BASIC_SKILL1 |
----+-------------------+-------------------+------------------+
1 | Java,C++ | Development | Software |
答
自己在询问同一个问题后发现您的问题。我想出的解决方案是使用带有DISTINCT的公用表表达式。
WITH q1 (id, specialization) AS
(
SELECT DISTINCT id, specialization
FROM table_name
)
SELECT q1.id,
XMLELEMENT(
NAME "Specializations",
XMLAGG(
XMLELEMENT(NAME "Specialization", q1.specialization)))
FROM q1
GROUP BY q1.id
在你的情况下,它会更容易和更清晰的使用子查询代替(XMLELEMENT样板省略掉的清晰度):
SELECT t.id, XMLAGG(q1.specialization), XMLAGG(q2.basic_skill2),
XMLAGG(q3.basic_skill1)
FROM table_name t,
(SELECT DISTINCT id, specialization FROM table_name) q1,
(SELECT DISTINCT id, basic_skill2 FROM table_name) q2,
(SELECT DISTINCT id, basic_skill1 FROM table_name) q3
WHERE t.id = q1.id AND t.id = q2.id AND t.id = q3.id
GROUP BY t.id
有可能是一个更简单的方法,但是这是我来与...一起。
另外,您可能想要利用XMLQUERY和XSLTRANSFORM等功能。比你手动操作的方式更简单,更不容易出错。
答
由于多个连接给出了每个连接的值的所有组合,所以在不存在重复表的情况下,select distinct将不起作用。这会导致聚合函数中出现重复。
我发现将group bys和聚合函数推送到from部分中的子查询会给出最好的结果。
SELECT t.id, q1.values, q2.values, q3.values
FROM table_name t,
inner join (select t1.id, listagg(t1.value,',') as values
from table_name1 t1 inner join table_name t on t.id=t1.id
group by t1.id) q1 on t.id = q1.id
inner join (select t2.id, listagg(t2.value,',') as values
from table_name2 t2 inner join table_name t on t.id=t2.id
group by t2.id) q2 on t.id = q2.id
inner join (select t3.id, listagg(t3.value,',') as values
from table_name3 t3 inner join table_name t on t.id=t3.id
group by t3.id) q3 on t.id = q3.id
您是否愿意发布表格的DDL以使人们更容易帮助您? – 2011-12-19 21:30:24
据我所知,在DB2中没有与GROUP_CONCAT类似的功能 – evermean 2013-03-06 13:49:35