SDP | 角色及关联技术员 报表

SDP | 角色及关联技术员 报表

测试环境

15230版本

效果

技术员格式为 first_name(loginName)

SQL

PGSQL 

Info
SELECT
    role_name AS "角色",
    string_agg(technician, ', ') AS "包含的技术员"
FROM (
    SELECT DISTINCT
        role_name,
        technician,
        sort_name
    FROM (
        SELECT
            ar.name AS role_name,
            aaauser.first_name || '(' || aaalogin.name || ')' AS technician,
            aaauser.first_name AS sort_name
        FROM aaauser
        JOIN sduser
             ON aaauser.user_id = sduser.userid
        JOIN portaltechnicians
             ON portaltechnicians.userid = aaauser.user_id
        JOIN aaalogin
             ON aaalogin.user_id = aaauser.user_id
        JOIN aaaaccount
             ON aaaaccount.login_id = aaalogin.login_id
        JOIN sdauthorizedrole aar
             ON aar.account_id = aaaaccount.account_id
        JOIN aaarole ar
             ON ar.role_id = aar.role_id
        WHERE sduser.status = 'ACTIVE'
    ) base
) dedup
GROUP BY role_name
ORDER BY role_name;

MSSQL


Info
SELECT role_name AS [角色], STRING_AGG(technician, ', ') AS [包含的技术员] FROM ( SELECT DISTINCT role_name, technician, sort_name FROM ( SELECT ar.name AS role_name, aaauser.first_name + '(' + aaalogin.name + ')' AS technician, aaauser.first_name AS sort_name FROM aaauser JOIN sduser ON aaauser.user_id = sduser.userid JOIN portaltechnicians ON portaltechnicians.userid = aaauser.user_id JOIN aaalogin ON aaalogin.user_id = aaauser.user_id JOIN aaaaccount ON aaaaccount.login_id = aaalogin.login_id JOIN sdauthorizedrole aar ON aar.account_id = aaaaccount.account_id JOIN aaarole ar ON ar.role_id = aar.role_id WHERE sduser.status = 'ACTIVE' ) base) dedup GROUP BY role_name ORDER BY role_name;