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

问题描述

我想要等同于DB2中MySql的GROUP_CONCAT功能。

I want equivalent to GROUP_CONCAT functionality of MySql in DB2.

我已经尝试使用DB2的XML Aggrigate功能来覆盖缪斯虫。

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 unique无效没有重复项，因为有多个联接提供了每个联接的所有值组合。这会导致聚合函数重复。

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 by和聚合函数推入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屋！