PgSQL
仅技术员报表
含：名称，登录名，主邮箱地址，部门名称，部门地点，员工id，电话，关联组，角色
SELECT max(aaauser.first_name) AS "名称",max(aaalogin.name) AS "登录名",max(aaacontactinfo.emailid) AS "主邮箱地址",max(departmentdefinition.deptname) AS "部门名称",max(sdorganization.NAME) AS "地点",max(sduser.EMPLOYEEID) AS "员工ID",max(aaacontact.LANDLINE) AS "电话",max(aaacontact.MOBILE) AS "手机" ,string_agg(qd.queuename,',') AS "关联组",string_agg( ar.NAME, ',' ) 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 userdepartment ON aaauser.user_id=userdepartment.userid LEFT JOIN departmentdefinition ON userdepartment.deptid=departmentdefinition.deptid LEFT JOIN sdorganization ON departmentdefinition.siteid = sdorganization.org_id LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id=aaauser.user_id LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id=aaacontactinfo.contactinfo_id LEFT JOIN AaaUserContactInfo aaausercontact ON aaauser.USER_ID=aaausercontact.USER_ID LEFT JOIN AaaContactInfo aaacontact ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.USER_ID LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID LEFT JOIN AaaLogin al ON aaauser.USER_ID=al.USER_ID LEFT JOIN AaaAccount aa ON al.LOGIN_ID=aa.LOGIN_ID LEFT JOIN sdAuthorizedRole aar ON aa.ACCOUNT_ID=aar.ACCOUNT_ID LEFT JOIN AaaRole ar ON aar.ROLE_ID=ar.ROLE_ID LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID WHERE sduser.status='ACTIVE' AND portaltechnicians.userid is not null GROUP BY aaauser.user_id;
所有用户报表
SELECT max(aaauser.first_name) AS "名称",max(aaalogin.name) AS "登录名",max(aaacontactinfo.emailid) AS "主邮箱地址",max(departmentdefinition.deptname) AS "部门名称",max(sdorganization.NAME) AS "地点",max(sduser.EMPLOYEEID) AS "员工ID",max(aaacontact.LANDLINE) AS "电话",max(aaacontact.MOBILE) AS "手机" ,string_agg(qd.queuename,',') AS "关联组",string_agg( ar.NAME, ',' ) 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 userdepartment ON aaauser.user_id=userdepartment.userid LEFT JOIN departmentdefinition ON userdepartment.deptid=departmentdefinition.deptid LEFT JOIN sdorganization ON departmentdefinition.siteid = sdorganization.org_id LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id=aaauser.user_id LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id=aaacontactinfo.contactinfo_id LEFT JOIN AaaUserContactInfo aaausercontact ON aaauser.USER_ID=aaausercontact.USER_ID LEFT JOIN AaaContactInfo aaacontact ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.USER_ID LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID LEFT JOIN AaaLogin al ON aaauser.USER_ID=al.USER_ID LEFT JOIN AaaAccount aa ON al.LOGIN_ID=aa.LOGIN_ID LEFT JOIN sdAuthorizedRole aar ON aa.ACCOUNT_ID=aar.ACCOUNT_ID LEFT JOIN AaaRole ar ON aar.ROLE_ID=ar.ROLE_ID LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID WHERE sduser.status='ACTIVE' GROUP BY aaauser.user_id;
MSSQL
仅技术员报表
含：名称，登录名，主邮箱地址，部门名称，部门地点，员工id，电话，关联组，角色
SELECT max(aaauser.first_name) AS "名称" ,max(aaalogin.name) AS "登录名" ,max(aaacontactinfo.emailid) AS "主邮箱地址" ,max(departmentdefinition.deptname) AS "部门名称" ,max(sdorganization.NAME) AS "地点" ,max(sduser.EMPLOYEEID) AS "员工ID" ,max(aaacontact.LANDLINE) AS "电话" ,max(aaacontact.MOBILE) AS "手机" ,STUFF((SELECT CHAR(10) + qd.queuename from AaaUser a LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.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 userdepartment ON aaauser.user_id=userdepartment.userid LEFT JOIN departmentdefinition ON userdepartment.deptid=departmentdefinition.deptid LEFT JOIN sdorganization ON departmentdefinition.siteid = sdorganization.org_id LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id=aaauser.user_id LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id=aaacontactinfo.contactinfo_id LEFT JOIN AaaUserContactInfo aaausercontact ON aaauser.USER_ID=aaausercontact.USER_ID LEFT JOIN AaaContactInfo aaacontact ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.USER_ID LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID LEFT JOIN AaaLogin al ON aaauser.USER_ID=al.USER_ID LEFT JOIN AaaAccount aa ON al.LOGIN_ID=aa.LOGIN_ID LEFT JOIN sdAuthorizedRole aar ON aa.ACCOUNT_ID=aar.ACCOUNT_ID LEFT JOIN AaaRole ar ON aar.ROLE_ID=ar.ROLE_ID LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID WHERE sduser.status='ACTIVE'AND portaltechnicians.userid is not null GROUP BY aaauser.user_id;
用户报表
含：名称，登录名，主邮箱地址，部门名称，关联的地点，员工id，电话，关联组，角色
SELECT max(aaauser.first_name) AS "名称" ,max(aaalogin.name) AS "登录名" ,max(aaacontactinfo.emailid) AS "主邮箱地址" ,max(departmentdefinition.deptname) AS "部门名称" ,max(sdorganization.NAME) AS "地点" ,max(sduser.EMPLOYEEID) AS "员工ID" ,max(aaacontact.LANDLINE) AS "电话" ,max(aaacontact.MOBILE) AS "手机" ,STUFF((SELECT CHAR(10) + qd.queuename from AaaUser a LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.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 userdepartment ON aaauser.user_id=userdepartment.userid LEFT JOIN departmentdefinition ON userdepartment.deptid=departmentdefinition.deptid LEFT JOIN sdorganization ON departmentdefinition.siteid = sdorganization.org_id LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id=aaauser.user_id LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id=aaacontactinfo.contactinfo_id LEFT JOIN AaaUserContactInfo aaausercontact ON aaauser.USER_ID=aaausercontact.USER_ID LEFT JOIN AaaContactInfo aaacontact ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.USER_ID LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID LEFT JOIN AaaLogin al ON aaauser.USER_ID=al.USER_ID LEFT JOIN AaaAccount aa ON al.LOGIN_ID=aa.LOGIN_ID LEFT JOIN sdAuthorizedRole aar ON aa.ACCOUNT_ID=aar.ACCOUNT_ID LEFT JOIN AaaRole ar ON aar.ROLE_ID=ar.ROLE_ID LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID WHERE sduser.status='ACTIVE' GROUP BY aaauser.user_id;