求一个SQL联合查询

[三碗猪脚]

现在有两个表
users 表
有两列
id,name
1 张三
2 李四
3 王五
4 周六
表groups
有四列
id,user1Id,user2Id,user3Id
1, 1,2,3
2,2,3,4
表groups记录的都是ID，我想查询结果增加三列把用户的姓名信息也加进去
如id,user1Id,name1,user2Id,name2,user3Id,name3
1,1,张三，2,李四，3，王五
2,2,李四，3,王五，4，周六

要求效率比较高，实际应用的数据量比较大，不要用嵌套查询

[百年树人]

select a.id,a.user1id,b.name as name1,a.user2id,c.name as name2,a.user3id,d.name as name3
from groups a
left join users b on a.user1id=b.id
left join users c on a.user2id=c.id
left join users d on a.user3id=d.id

[三碗猪脚]

原来可以搞几个别名，3Q

[tommore]

select a.id,
            a.user1id, (select name from users t1 where t1.user1id = a.user1id) name1,
            a.user2id, (select name from users t2 where t2.user1id = a.user1id) name2,
            a.user3id, (select name from users t3 where t3.user1id = a.user1id) name3,
from groups a