技术员名称，邮箱，域名，所在组，所包含角色权限报表
技术员名称，邮箱，域名，所在组，所包含角色权限报表
MSSQL：
SELECT
MAX(aaauser.first_name) AS "名称",
MAX(aaalogin.name) AS "登录名",
MAX(aaacontactinfo.emailid) AS "主邮箱地址",
MAX(aaalogin.domainname) AS "域名",
STUFF((
SELECT CHAR(10) + qd.queuename
FROM AaaUser a
LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID = a.USER_ID
LEFT JOIN QueueDefinition qd ON qt.QUEUEID = qd.QUEUEID
WHERE aaauser.user_id = a.user_id
FOR XML PATH ('')
), 1, 1, '') AS "所在支持组",
-- 角色字段
STUFF((
SELECT CHAR(10) + aar.name
FROM aaauser a
LEFT JOIN aaalogin al ON al.user_id = a.user_id
LEFT JOIN aaaaccount aaac ON al.login_id = aaac.login_id
LEFT JOIN SDAuthorizedRole sdr ON aaac.account_id = sdr.account_id
LEFT JOIN aaarole aar ON aar.role_id = sdr.role_id
WHERE aaauser.user_id = a.user_id
FOR XML PATH ('')
), 1, 1, '') AS "角色"
FROM aaauser
LEFT JOIN sduser ON aaauser.user_id = sduser.userid
LEFT JOIN aaauser sdus ON sduser.REPORTINGTO = sdus.USER_ID
LEFT JOIN aaalogin ON aaalogin.user_id = aaauser.user_id
LEFT JOIN aaaaccount ON aaalogin.login_id = aaaaccount.login_id
LEFT JOIN portaltechnicians ON aaauser.user_id = portaltechnicians.userid
LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id = aaauser.user_id
LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id = aaacontactinfo.contactinfo_id
WHERE sduser.status = 'ACTIVE'
AND portaltechnicians.userid IS NOT NULL
GROUP BY aaauser.user_id;
PGSQL：
SELECT
MAX(aaauser.first_name) AS "名称",
MAX(aaalogin.name) AS "登录名",
MAX(aaacontactinfo.emailid) AS "主邮箱地址",
MAX(aaalogin.domainname) AS "域名",
COALESCE(
(SELECT STRING_AGG(q_name, CHR(10))
FROM (SELECT DISTINCT qd.queuename AS q_name
FROM Queue_Technician qt
LEFT JOIN QueueDefinition qd ON qt.QUEUEID = qd.QUEUEID
WHERE qt.TECHNICIANID = aaauser.USER_ID
ORDER BY q_name) AS temp_q),
''
) AS "所在支持组",
COALESCE(
(SELECT STRING_AGG(r_name, CHR(10))
FROM (SELECT DISTINCT aar.name AS r_name
FROM aaalogin al
LEFT JOIN aaaaccount aaac ON al.login_id = aaac.login_id
LEFT JOIN SDAuthorizedRole sdr ON aaac.account_id = sdr.account_id
LEFT JOIN aaarole aar ON aar.role_id = sdr.role_id
WHERE al.user_id = aaauser.USER_ID
ORDER BY r_name) AS temp_r),
''
) AS "角色"
FROM aaauser
LEFT JOIN sduser ON aaauser.user_id = sduser.userid
LEFT JOIN aaauser sdus ON sduser.REPORTINGTO = sdus.USER_ID
LEFT JOIN aaalogin ON aaalogin.user_id = aaauser.user_id
LEFT JOIN aaaaccount ON aaalogin.login_id = aaaaccount.login_id
LEFT JOIN portaltechnicians ON aaauser.user_id = portaltechnicians.userid
LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id = aaauser.user_id
LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id = aaacontactinfo.contactinfo_id
WHERE sduser.status = 'ACTIVE'
AND portaltechnicians.userid IS NOT NULL
GROUP BY aaauser.user_id