Отчет для отображения пользователей и их соответствующих ролей, назначенных затем, также отображает количество ролей, назначенных конкретным пользователям

#sql #oracle

Вопрос:

 SELECT 
    pu.username, prdt.role_name, prdt.description, 
    prd.role_common_name, COUNT(*) cnt   --> this
FROM 
    per_users pu 
JOIN
    per_user_roles pur ON pur.user_id = pu.user_id 
JOIN
    per_roles_dn prd ON prd.role_id = pur.role_id 
JOIN
    per_roles_dn_tl prdt ON prdt.role_id = prd.role_id
GROUP BY 
    pu.username, --> and this prdt.role_name, prdt.description, prd.role_common_name

Может ли кто-нибудь помочь мне получить еще один столбец количества ролей, назначенных определенному столбцу пользователей?

Ответ №1:

Если вам нужно отобразить pu.username, prdt.role_name, prdt.description, prd.role_common_name вместе с количеством ролей на пользователя, то вам не нужно предложение group by, а функция окна count()over() сделает свое дело.

 SELECT pu.username, prdt.role_name, prdt.description, prd.role_common_name, count(prdt.role_name)over(partition by pu.username) role_count_per_user
FROM per_users pu JOIN
     per_user_roles pur
     ON pur.user_id = pu.user_id JOIN
     per_roles_dn prd
     ON prd.role_id = pur.role_id JOIN
     per_roles_dn_tl prdt
    ON prdt.role_id = prd.role_id

Но если есть возможность назначить одну и ту же роль пользователю несколько раз (маловероятно), и вам нужно это количество, то группировка по-это правильный подход.

 SELECT pu.username, prdt.role_name, prdt.description, prd.role_common_name, COUNT(*)cnt
FROM per_users pu JOIN
     per_user_roles pur
     ON pur.user_id = pu.user_id JOIN
     per_roles_dn prd
     ON prd.role_id = pur.role_id JOIN
     per_roles_dn_tl prdt
    ON prdt.role_id = prd.role_id
GROUP BY pu.username, and this prdt.role_name, prdt.description, prd.role_common_name