2019-01-16 · Develop

Oracle 根据字段去重和字符串拼接

场景

有时会遇到这么个情况,有用户(user) 、角色(role)和关系表(user_role)三张表来保存用户和角色的信息。由于一个用户可以绑定多个角色,在页面展示用户列表的时候普通的连表查询就会造成出现重复用户的情况,这个时候对用户的展示就有两种情况

初始化信息

三张表的数据如下

用户表 USER

+----+--------+
| ID | NAME   |
+----+--------+
|  1 | USER_1 |
|  2 | USER_2 |
|  3 | USER_3 |
+----+--------+

角色表 ROLE

+----+--------+
| ID | NAME   |
+----+--------+
|  1 | ROLE_1 |
|  2 | ROLE_2 |
|  3 | ROLE_3 |
+----+--------+

用户和角色关系表 USER_ROLE

+---------+---------+
| USER_ID | ROLE_ID |
+---------+---------+
|       1 |       1 |
|       1 |       2 |
|       2 |       2 |
|       2 |       3 |
|       3 |       1 |
|       3 |       2 |
|       3 |       3 |
+---------+---------+

单用户展示单角色

单用户展示单个角色,说的简单就是通过 USER_ID 进行去重,可以使用开窗函数 ROW_NUMBER 来处理

SELECT USER_ID, ROLE_ID
FROM (
    SELECT USER_ID, ROLE_ID, 
    ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY USER_ID) AS ROW_ID 
    FROM "USER_ROLE"
) WHERE ROW_ID = 1

ROW_NUMBER() OVER() 函数就是将数据进行分组排序,然后通过排序的序号就行取第一条数据,就达到了我们的需求,这个时候再和 USER 以及 ROLE 表进行联表查询得到想要的结果。

SELECT
  u.NAME AS USER_NAME, r.NAME AS ROLE_NAME
FROM "USER" u
LEFT JOIN (
    SELECT USER_ID, ROLE_ID
    FROM (
        SELECT USER_ID, ROLE_ID, ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY USER_ID) AS ROW_ID FROM "USER_ROLE"
    ) WHERE ROW_ID = 1
) ur ON u.ID = ur.USER_ID
LEFT JOIN "ROLE" r ON ur.ROLE_ID = r.ID
ORDER BY u.ID;

角色拼接展示

将角色进行拼接需要先进行拼接操作再和 USER 表进行联表

SELECT ur.USER_ID, WMSYS.WM_CONCAT(r.NAME) AS ROLE_NAMES
FROM "ROLE" r
LEFT JOIN "USER_ROLE" ur ON r.ID = ur.ROLE_ID
GROUP BY ur.USER_ID

将上面的拼接信息和 USER 表联表就能得到想要的结果

SELECT u.NAME AS USER_NAME, r.ROLE_NAMES
FROM "USER" u
LEFT JOIN (
  SELECT ur.USER_ID, WMSYS.WM_CONCAT(r.NAME) AS ROLE_NAMES
  FROM "ROLE" r
  LEFT JOIN "USER_ROLE" ur ON r.ID = ur.ROLE_ID
  GROUP BY ur.USER_ID
) r ON u.ID = r.USER_ID

函数 WMSYS.WM_CONCAT 的拼接只能是 “,” 并且 Oracle 以及不推荐使用了,所以可以使用函数 listagg 函数进行替代

SELECT ur.USER_ID, LISTAGG(r.NAME, ',') WITHIN GROUP (ORDER BY ur.USER_ID)
AS ROLE_NAMES
FROM "ROLE" r
LEFT JOIN "USER_ROLE" ur ON r.ID = ur.ROLE_ID
GROUP BY ur.USER_ID