Re: 多表联合查询,使用json_arrayagg会生成多条重复的数据
Posted by:
bill lin
Date: January 20, 2020 08:33PM
请问如何能去除掉json_arrayagg中重复的数据
一、下面是查询的sql:
SELECT
JSON_PRETTY(
JSON_OBJECT(
'username',
a.username,
'password',
a.PASSWORD,
'payPassword',
a.pay_password,
'nameCn',
a.name_cn,
'roles',
JSON_ARRAYAGG(
JSON_OBJECT(
'roleCode',
c.role_code,
'roleName',
c.role_name
)),
'menus',
JSON_ARRAYAGG(
JSON_OBJECT(
'parentId',
e.parent_id,
'menuName',
e.menu_name,
'path',
e.path,
'menuLevel',
e.menu_level
)),
'privs',
JSON_ARRAYAGG(
JSON_OBJECT(
'menuId',
g.menu_id,
'privCode',
g.priv_code,
'privName',
g.priv_name
))
)
)
FROM
t_user a,
user_role b,
base_role c,
role_menu d,
base_menu e,
menu_priv f,
base_priv g
WHERE
a.id = 1
AND a.id = b.user_id
AND c.id = d.role_id
AND d.menu_id = e.id
and f.user_id=a.id
and f.menu_id=e.id
and f.priv_id=g.id;
二、下面是返回值
{
"menus": [
{
"path": "/index.html",
"menuName": "首页",
"parentId": null,
"menuLevel": 1
},
{
"path": "/index.html",
"menuName": "首页",
"parentId": null,
"menuLevel": 1
},
{
"path": "/left.html",
"menuName": "左边框",
"parentId": 1,
"menuLevel": 2
},
{
"path": "/left.html",
"menuName": "左边框",
"parentId": 1,
"menuLevel": 2
},
{
"path": "/right.html",
"menuName": "右边框",
"parentId": 1,
"menuLevel": 3
},
{
"path": "/right.html",
"menuName": "右边框",
"parentId": 1,
"menuLevel": 3
}
],
"privs": [
{
"menuId": 2,
"privCode": "indexDel",
"privName": "首页删除按钮"
},
{
"menuId": 2,
"privCode": "indexSubmit",
"privName": "首页提交按钮"
},
{
"menuId": 3,
"privCode": "leftDel",
"privName": "左边框删除按钮"
},
{
"menuId": 3,
"privCode": "leftSubmit",
"privName": "左边框提交按钮"
},
{
"menuId": 4,
"privCode": "rightQuery",
"privName": "右边框查询按钮"
},
{
"menuId": 4,
"privCode": "rightUpdate",
"privName": "右边框更新按钮"
}
],
"roles": [
{
"roleCode": "admin",
"roleName": "管理员"
},
{
"roleCode": "admin",
"roleName": "管理员"
},
{
"roleCode": "admin",
"roleName": "管理员"
},
{
"roleCode": "admin",
"roleName": "管理员"
},
{
"roleCode": "admin",
"roleName": "管理员"
},
{
"roleCode": "admin",
"roleName": "管理员"
}
],
"nameCn": "张闯",
"password": "112409",
"username": "zhangchuang",
"payPassword": "990522"
}
问题就在这儿,roles和menus中的值重复,因为是关联查询,所以导致生成了多条记录。
三、下面是原始数据
(1)去掉json的处理改为*
SELECT
*
FROM
t_user a,
user_role b,
base_role c,
role_menu d,
base_menu e,
menu_priv f,
base_priv g
WHERE
a.id = 1
AND a.id = b.user_id
AND c.id = d.role_id
AND d.menu_id = e.id
AND f.user_id = a.id
AND f.menu_id = e.id
AND f.priv_id = g.id;
(2)下面是查询到的值
id username password pay_password name_cn name_cn_pinyin name_en tel phone position job email state remark id(1) user_id role_id id(2) role_code role_name remark(1) id(3) role_id(1) menu_id id(4) parent_id menu_name path menu_level state(1) id(5) user_id(1) menu_id(1) priv_id id(6) menu_id(2) priv_code priv_name
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 1 1 2 2 首页 /index.html 1 1 2 1 2 2 2 2 indexDel 首页删除按钮
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 1 1 2 2 首页 /index.html 1 1 1 1 2 1 1 2 indexSubmit 首页提交按钮
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 2 1 3 3 1 左边框 /left.html 2 1 4 1 3 6 6 3 leftDel 左边框删除按钮
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 2 1 3 3 1 左边框 /left.html 2 1 3 1 3 5 5 3 leftSubmit 左边框提交按钮
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 3 1 4 4 1 右边框 /right.html 3 1 6 1 4 12 12 4 rightQuery 右边框查询按钮
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 3 1 4 4 1 右边框 /right.html 3 1 5 1 4 11 11 4 rightUpdate 右边框更新按钮
三、这个账号是我再网上找的oracle账号,我自己的邮箱是724914749@qq.com,请联系我,谢谢