DB2 Distinct + xmlagg查询
|
我想要等同于DB2中MySql的GROUP_CONCAT功能。
我已经尝试过使用DB2的XML Aggrigate功能来整理缪斯。
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 |
ID | SPECIALISATION | BASIC_SKILL2 | BASIC_SKILL1 |
----+-------------------+-------------------+------------------+
1 | Java,C++ | Development | Software |
没有找到相关结果
已邀请:
2 个回复
夏瓤跋棘
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.idSELECT 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峨躬坎抬焚
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