group-by - DB2 独特 + xmlagg 查询

Question

我想要相当于 DB2 中 MySql 的 GROUP_CONCAT 功能。

我尝试过 DB2 的 XML Aggrigate 函数来合并 murows。

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       |

Answer 1

我自己问过同样的问题后才发现你的问题。我想出的解决方案是使用带有 DISTINCT 的公共(public)表表达式。

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 等函数。比手动方式更简单且不易出错。