一种失传已久的 SQL 多表连接查询，你从来没有见过的独门写法

从 Left Join 说起

假设你有这样一个 n2n 的关系表，代表用户和角色之间的关系。

通常通过 left join 去连接这三张表，来查询出用户及其角色的信息。

sql

代码解读

复制代码

SELECT u.id AS user_id, u.name AS user_name, r.name AS role_name FROM "user" u LEFT JOIN "user_role_map" urm ON u.id = urm.user_id LEFT JOIN "role" r ON urm.role_id = r.id;

user_id	user_name	role_name
1	Alice	Admin
1	Alice	User
2	Bob	User
3	Charlie	Guest

查询出的结果中，Alice 这个用户出现了两次。这是显而易见的，因为这是一个 "Flatten" 的结果。

这样的结果是无法返回给客户端直接使用的。你需要进行处理，把重复的用户归纳到一起以后再返回给客户端进行展示，比如像下面这样：

user_id	user_name	user's_role_array	备注
1	Alice	[(Admin),(User)]	在界面上以数组的形式供前端获取
2	Bob	User
3	Charlie	Guest

不幸的是这样的处理非常麻烦，你 join 的表越多这个代码越不好写，不相信你可以试试。

谈谈 ORM

有没有方便的方法来获取这个「嵌套」的结果呢？使用 Hibernate 这样的 ORM 框架是个不错的主意：

java

代码解读

复制代码

@Entity public class User { @Id private int id; @Column(name = "name", nullable = false) private String name; @ManyToMany @JoinTable( name = "user_role_map", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id") ) private Set<Role> roles; } @Entity public class Role { @Id private int id; @Column(name = "name", nullable = false) private String name; @ManyToMany(mappedBy = "roles") private Set<User> users; }

Hibernate 直接帮你把数据库的结果映射到了嵌套结果集中。现在你可以直接把 List<User> 返回给客户端了，因为这个结果现在展示为：

user_id	user_name	user's_role
1	Alice	Admin,User
2	Bob	User
3	Charlie	Guest

Hibernate 的问题

使用 Hibernate 的代价就是你的心智负担很大。除了要学习很多注解以外，还有很多夸张的概念需要深入理解，才能够写出能正常运行的代码。

那么，有没有一种简单的方法，可以通过 SQL 的方式，直接查询出这种嵌套的结果集， 然后扔给客户端进行处理呢？答案是有的。

全新的解决方案

java

代码解读

复制代码

public static void main(String[] args) { UserRoleEntity userRoleEntity = select( USER.ID, USER.NAME, array(select(ROLE.ID, ROLE.NAME) .from(ROLE) .join(USER_ROLE_MAP).on(ROLE.ID.eq(USER_ROLE_MAP.ROLE_ID)) .where(USER_ROLE_MAP.USER_ID.eq(USER.ID)) ).as("roles") ).from(USER); System.out.println(userRoleEntity); } class UserRoleEntity { private Long id; private String name; private List<Role> roles; }