通过SQL查询获取技术员信息进行导出（数据库SQL Server）

通过SQL查询获取技术员信息进行导出（数据库SQL Server）

      在11版本及更新的版本中，在后面版本中表结构发生了改变，取消了通过cmdb模块直接生成关于技术员信息的报表，变为了只能导出所有用户，而不再分技术员还是普通请求人。

      如果有通过报表生成技术员信息的需求，可以参考如下SQL语句进行出具（SQL Server）
核心表参考（portaltechnicians）
可以基于此再进行调整出具需要的关联查询。

SELECT  max(aaauser.first_name)            AS "Name"
       ,max(aaalogin.name)                 AS "Login Name"
       ,max(aaacontactinfo.emailid)        AS "Primary Email"
       ,max(departmentdefinition.deptname) AS "Department Name"
       ,max(sdorganization.NAME)           AS "Site"
       ,max(sduser.jobtitle)               AS "Job title"
       ,max(sdus.first_name)               AS "Reporting to"
       ,max(aaalogin.domainname)           AS "Domain Name"
       ,"Assigned Roles" = 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, '')
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
WHERE sduser.status='ACTIVE'
AND portaltechnicians.userid is not null
GROUP BY  aaauser.user_id;