一个项目的数据库设计很奇葩,用户信息表里有公司编号和角色编号,如果身兼多职,roleID就追加,用逗号分隔。。。。上传文件一律进file表,头像上传也不例外,然后,file表有个关联字段,保存所有者的编号。。。。
现在要取公司员工信息,用orm框架写太麻烦,就改用sql语句取,代码如下:
SELECT p.xGUID AS id
,p.Name AS name
,p.Account AS account
,p.Email AS email
,p.Sex As gender
,p.Mobile As phone
,p.IsExpert As isExpert
,p.ExpertArea As expertArea
,STUFF((
SELECT ',' + r.Name
FROM UE_Organization r
WHERE p.RoleID like '%' + CAST(r.xGUID AS VARCHAR(50)) + '%'
FOR XML PATH('')),1,1,'') AS roleName
,(SELECT TOP 1 f.Path
FROM UE_File f
WHERE f.LYBMID = p.xGUID
AND f.ScenesType = N'头像'
ORDER BY CreateTime DESC) AS photo
FROM UE_Person p
JOIN UE_Organization c
ON p.CompanyID = c.xGUID
WHERE c.xGUID = @cid;
虽然奇葩,但里面有几个知识点,以后可能会用到,所以先记录下来,以备后用