56,677
社区成员
发帖
与我相关
我的任务
分享
SELECT
user.Name AS UserName,
role.Name AS UserRoleName,
sendUser.Name AS SendUserName,
sendRole.Name AS SendUserRoleName
FROM
Message
LEFT JOIN
User user ON Message.UserID1 = user.id
LEFT JOIN
Role role ON user.RoleID = role.id
LEFT JOIN
User sendUser ON Message.UserID1 = sendUser.id
LEFT JOIN
Role sendRole ON sendUser.RoleID = sendRole.id
create temporary table if not exists tmp_userrole
select a.id userid, b.id roleid, a.name username,b.name rolename FROM user a inner join role b on a.roleid = b.roleid;
select
(select name from user where userid = t.userid) UserName
,(select rolename from tmp_userrole where userid = t.userid) UserRoleName
,(select name from user where userid = t.SendUserID) SendUserName
,(select rolename from tmp_userrole where userid = t.SendUserID) SendUserRoleName
from Message t ;