我有一个查询,该查询使所有用户都具有报表管理器。我想通过报告经理电子邮件ID将用户ID分组为列表。
SELECT q.USER_ID,q.REPORTS_TO,e.EMAIL
FROM (SELECT a.USER_ID,a.REPORTS_TO,a.EMAIL
FROM PortalX.PTX_USERS a
LEFT OUTER JOIN PortalX.PTX_LDAP_USERS b
ON a.USER_ID = b.OWN_ID
WHERE b.OWN_ID IS NULL) q
INNER JOIN PortalX.PTX_USERS e ON q.REPORTS_TO = e.USER_ID;
我对编写oracle查询不是很熟悉。任何帮助表示赞赏。
编辑:
在@Barbaros的帮助下,她是最终查询,该查询将用户聚合为用逗号分隔的值的列表。
SELECT a.REPORTS_TO,a.EMAIL,LISTAGG(a.USER_ID,',') WITHIN GROUP (ORDER BY a.USER_ID) as USERS
FROM PortalX.PTX_USERS a
LEFT JOIN PortalX.PTX_LDAP_USERS b
ON a.USER_ID = b.OWN_ID
AND a.REPORTS_TO = a.USER_ID
WHERE b.OWN_ID IS NULL
GROUP BY a.REPORTS_TO,a.EMAIL;