Obtenir des lignes distinctes d'une jointure intérieure

Vous pouvez utiliser Listagg code> fonction d'agrégat avec Groupe par code> comme suit:

SELECT user.last_name,
       user.first_name, 
       user.user_name, 
       listagg(group.name,',') within group (order by group.name) as groupss,
       listagg(role.name,',') within group (order by role.name) as roless 
FROM user  INNER JOIN user_group ON user_group.user_fk = user.id 
INNER JOIN group ON group.id = user_group.group_fk 
INNER JOIN group_role ON group.id = group_role.group_fk 
INNER JOIN role ON group_role.role_fk = role.id 
Group by  user.last_name, 
          user.first_name, 
          user.user_name;

Essayez quelque chose comme ceci:

SELECT
user.last_name,
user.first_name,
user.user_name,
listagg(group.name || ' - ' ||role.name, ', ') within group (order by group.name, role.name)
FROM user 
INNER JOIN user_group ON user_group.user_fk = user.id
INNER JOIN group ON group.id = user_group.group_fk
INNER JOIN group_role ON group.id = group_role.group_fk
INNER JOIN role ON group_role.role_fk = role.id
group by user.last_name,
user.first_name,
user.user_name

Je recommanderais deux sous-requêtes. Pourquoi? Les rôles peuvent être répétés et listagg () code> ne prend pas en charge distinct code>.

donc: p> xxx pre>

vous pouviez Écrivez également ceci en utilisant une sous-requête et une agrégation conditionnelle: P>

select u.last_name, u.first_name, u.user_name, 
       listagg(case when seqnum_g = 1 then ugr.name end, ',') within group (order by ug.name) as groups,
       listagg(case when seqnum_r = 1 then ugr.name end, ',') as roles within group (order by ur.name) as roles 
from user u left join
     (select ug.user_fk,
             row_number() over (partition by ug.user_fk order by g.name) as seqnum_g,
             row_number() over (partition by ug.user_rk order by r.name) as seqnum_r
      from user_group ug join
           group g join
           on g.id = ug.group_fk join
           group_role gr
           on g.id = gr.group_fk join
           role r
           on gr.role_fk = r.id 
     ) ugr
     on ugr.user_fk = u.id
group by u.last_name, u.first_name, u.user_name;