DB2 Distinct + xmlagg 查询 [英] DB2 Distinct + xmlagg Query

问题描述

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

I want equivalent to GROUP_CONCAT functionality of MySql in DB2.

我已经尝试过 DB2 的 XML Aggregate 函数来处理 murows.

I have tried XML Aggrigate functon of DB2 for cocating 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 的独特/唯一值.

But I want distinct/Unique Value of BASIC_SKILL2,BASIC_SKILL1.

ID  |    SPECIALISATION |    BASIC_SKILL2   |   BASIC_SKILL1   |
----+-------------------+-------------------+------------------+
1   |    Java,C++       |  Development      |   Software       |

推荐答案

select distinct 在没有重复的表的情况下不起作用，因为多个连接给出了每个连接值的所有组合.这会导致聚合函数出现重复.

The select distinct won't work in case of tables with no duplicates, because of multiple joins giving all the combinations of the values of every join. That leads to duplicates in the aggregate function.

我发现将 group bys 和聚合函数推送到 from 部分中的子查询可以获得最佳结果.

I've found that pushing the group bys and the aggregate functions to subqueries in the from part gives the best results.

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

这篇关于DB2 Distinct + xmlagg 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！