===》分析领导的实现过程。心得笔记。
===》功能需求(补充):
根据企业编号(sql入参)查询:
当前登录用户所在的企业下所有的角色列表+每个角色下的权限个数、人员个数。
#业务场景:不同企业下有各自的角色。角色下有权限(角色:权限=多对多,有中间表)和用户(角色:用户=一对多,用户表有个角色id)
#表结构:看文件。
#需求:登录用户所属企业id,
#1
SELECT a.PB_ROL_code,a.PB_ROL_name, b.PB_UCT_nickname,COUNT(b.PB_UCT_no) -- , c.PB_PER_code,COUNT(c.PB_PER_code)
from pb_combaseinfo m
INNER JOIN pb_role a ON m.PB_CMB_comno = a.PB_ROL_ownedcomno AND a.PB_ROL_ownedcomno = 8000002
LEFT JOIN pb_usract b ON b.PB_UCT_roleno = a.PB_ROL_code AND b.PB_UCT_ownedcom = a.PB_ROL_ownedcomno GROUP BY a.PB_ROL_code
#1 V2
SELECT a.PB_ROL_code,a.PB_ROL_name, b.PB_UCT_nickname,COUNT(b.PB_UCT_no) -- , c.PB_PER_code,COUNT(c.PB_PER_code)
from pb_combaseinfo m
INNER JOIN pb_role a ON m.PB_CMB_comno = a.PB_ROL_ownedcomno
LEFT JOIN pb_usract b ON b.PB_UCT_roleno = a.PB_ROL_code AND b.PB_UCT_ownedcom = a.PB_ROL_ownedcomno GROUP BY a.PB_ROL_code
-- WHERE a.PB_ROL_ownedcomno = 8000002 #语法报错。
WHERE m.PB_CMB_comno = 8000002 #语法报错。
#1 V3
SELECT a.PB_ROL_code,a.PB_ROL_name, b.PB_UCT_nickname,COUNT(b.PB_UCT_no) -- , c.PB_PER_code,COUNT(c.PB_PER_code)
from pb_combaseinfo m
INNER JOIN pb_role a ON m.PB_CMB_comno = a.PB_ROL_ownedcomno
LEFT JOIN pb_usract b ON b.PB_UCT_roleno = a.PB_ROL_code AND b.PB_UCT_ownedcom = a.PB_ROL_ownedcomno
WHERE a.PB_ROL_ownedcomno = 8000002 # 或 WHERE m.PB_CMB_comno = 8000002 都一样。
-- 只要条件加对了,就行!!!(条件的位置调整+SQL基础语法)【where提取到最后(不放在查询角色后面),group by 必须要放到where后面。】
GROUP BY a.PB_ROL_code
#1 V3 》基础上 添加 统计 权限个数。ok
SELECT a.PB_ROL_code,a.PB_ROL_name, b.PB_UCT_nickname,COUNT( DISTINCT b.PB_UCT_no), c.PB_PER_code,COUNT(DISTINCT c.PB_PER_code)
from pb_combaseinfo m
INNER JOIN pb_role a ON m.PB_CMB_comno = a.PB_ROL_ownedcomno
LEFT JOIN pb_usract b ON b.PB_UCT_roleno = a.PB_ROL_code AND b.PB_UCT_ownedcom = a.PB_ROL_ownedcomno
LEFT JOIN pb_perole c ON c.PB_ROL_code = a.PB_ROL_code #1 V3 基础上 添加 统计 权限。
WHERE a.PB_ROL_ownedcomno = 8000002 # 或 WHERE m.PB_CMB_comno = 8000002 都一样。
-- 只要条件加对了,就行!!!(条件的位置调整+SQL基础语法)【where提取到最后(不放在查询角色后面),group by 必须要放到where后面。】
-- 【再关联 角色权限表后,统计的用户个数,也变化了!》》》使用distinct 约束后恢复原样。
-- 》》》###要写出来这个约束,###必须很清楚自己现在查出来的用户个数的数据是如何出来的:
-- 统计用户个数有几个,相当于后面可以扩展出多少条用户记录。所以因为之前统计的用户个数比较少,可以猜到:用户记录一定是重复了!】
GROUP BY a.PB_ROL_code
#2
SELECT a.PB_ROL_code,a.PB_ROL_name
-- , b.PB_UCT_nickname,COUNT(b.PB_UCT_no)
, c.PB_PER_code,COUNT(c.PB_PER_code)
from pb_combaseinfo m
INNER JOIN pb_role a ON m.PB_CMB_comno = a.PB_ROL_ownedcomno AND a.PB_ROL_ownedcomno = 8000002
-- LEFT JOIN pb_usract b ON b.PB_UCT_roleno = a.PB_ROL_code AND b.PB_UCT_ownedcom = a.PB_ROL_ownedcomno GROUP BY a.PB_ROL_code
LEFT JOIN pb_perole c ON c.PB_ROL_code = a.PB_ROL_code GROUP BY c.PB_ROL_code
#3 【简单的多个join放到一起,不行。】
SELECT a.PB_ROL_code,a.PB_ROL_name
, b.PB_UCT_nickname,COUNT(b.PB_UCT_no)
, c.PB_PER_code,COUNT(c.PB_PER_code)
from pb_combaseinfo m
INNER JOIN pb_role a ON m.PB_CMB_comno = a.PB_ROL_ownedcomno AND a.PB_ROL_ownedcomno = 8000002
LEFT JOIN pb_usract b ON b.PB_UCT_roleno = a.PB_ROL_code AND b.PB_UCT_ownedcom = a.PB_ROL_ownedcomno GROUP BY a.PB_ROL_code
-- LEFT JOIN pb_perole c ON c.PB_ROL_code = a.PB_ROL_code GROUP BY c.PB_ROL_code
LEFT JOIN pb_perole c ON c.PB_ROL_code = b.PB_ROL_code
#answer 领导
SELECT
c.PB_ROL_code as roleID
,c.PB_ROL_name as roleName
,c.PB_ROL_desc as roleDesc
,count(DISTINCT a.PB_UCT_no) AS peoCount
,count(DISTINCT d.PB_PER_code) as perCount
FROM
pb_combaseinfo b
LEFT JOIN pb_role c ON c.PB_ROL_ownedcomno = b.PB_CMB_comno
LEFT JOIN pb_usract a ON a.PB_UCT_roleno = c.PB_ROL_code AND a.PB_UCT_ownedcom = b.PB_CMB_comno
LEFT JOIN pb_perole d ON d.PB_ROL_code = c.PB_ROL_code AND a.PB_UCT_roleno = d.PB_ROL_code
WHERE b.PB_CMB_comno = 8000002 #{enterpriseId,jdbcType=VARCHAR}
GROUP BY
c.PB_ROL_name
===》语雀接口文档。
查看企业角色
请求方式
GET
调用的url
modules/intersert/action/getRolesForEnterprise.htm |
传参
无。(已登录用户才能调用此接口,登录用户从Redis取查询条件:企业编号:Redis key:"ENTERUUID")
逻辑
-
判断Redis里“是否是管理员”key的值,判断登录用户是不是管理员。是管理员才继续查询。不是返回错误提示“不是管理员”。
-
从Redis取查询条件:企业编号:Redis key:"ENTERUUID"。(因为Redis的key是token,首先要获取token。app登录请求有参数token,web登录需要从session里取token。逻辑封装到了BaseAction的getToken方法。)
-
根据企业编号查询:当前登录用户所在的企业下所有的角色列表。
-
同时查询出每个角色拥有的权限个数、每个角色对应的当前企业的用户个数。
返回值
已经存在数据
,返回json:
[ {
"roleID" : 0,
"roleName" : "管理员",
"roleDesc" : null,
"peoCount" : 10001,
"perCount" : 1
}, {
"roleID" : 0,
"roleName" : "经办员",
"roleDesc" : null,
"peoCount" : 10001,
"perCount" : 1
} ]
其他错误
{
"msg": "PB0000-系统发生错误",
"code": 400
}
===================================================补充20190529:【成长+雪前耻】
关系:
企业:角色 = 一对多
角色:用户 = 一对多
角色:权限 = 多对多 (有中间表)
功能需求:
根据企业编号(sql入参)查询:
当前企业下所有的角色列表(id,名称)+每个角色下的用户个数、权限个数。
----------------------当前系统类似业务:----------------------------
=====tam系统类似上述的关系:
商务项目:子项目 = 一对多
子项目:问题 = 一对多
子项目:风险点 = 多对多 (有中间表)
=====tam系统类似上述的功能需求:
根据商务项目编号(sql入参)查询:
当前商务项目下所有的子项目列表(id,名称)+每个子项目下的问题个数、风险点个数。
/*商务项目: 浙江政务云一朵云*/
SELECT * FROM indexshow_project a WHERE a.deliver_or_ops=1 AND a.is_valid=1 and id = 11234
SELECT * FROM deliver_subproject a WHERE a.is_valid=1 and a.project_id = 11234
/* count必须要去重+count(distinct) count 内部可以直接加distinct,不需要子查询结果集之后再count!!! */
SELECT b.project_id, b.subproject_id ,b.subproject_name,COUNT(DISTINCT c.issue_id),COUNT(DISTINCT e.riskid_uuid)
FROM indexshow_project a
LEFT JOIN deliver_subproject b on b.project_id = a.id
LEFT JOIN deliver_question c on c.subproject_id = b.subproject_id
LEFT JOIN indexshow_proriskstatus d on d.subproject_id = b.subproject_id
-- INNER JOIN indexshow_risktotal e on e.riskid_uuid = d.riskid_id /*60*/
LEFT JOIN indexshow_risktotal e on e.riskid_uuid = d.riskid_id
WHERE
a.deliver_or_ops=1
AND a.is_valid=1
and a.id = 11234
AND b.is_valid=1
AND c.is_valid=1
and d.is_valid=1
and d.deliver_or_ops=1
and e.is_valid=1
and e.approved_status=0
GROUP BY b.subproject_id
ORDER BY b.subproject_id
-- ==================== 上面是:一个商务项目的子项目列表(id,名称)+每个子项目下的问题个数、风险点个数。
-- ==================== 拓展:每个商务项目的子项目列表(id,名称)+每个子项目下的问题个数、风险点个数。
/*商务项目: 浙江政务云一朵云*/
SELECT * FROM indexshow_project a WHERE a.deliver_or_ops=1 AND a.is_valid=1 and id = 11234
SELECT * FROM deliver_subproject a WHERE a.is_valid=1 and a.project_id = 11234
/* count必须要去重+count(distinct)
======成长:查询、计数 习惯都加上distinct,一定没错;不加distinct,多表join时候,一定会出现 计数重复问题!!!
======count() 内部可以直接加distinct,不需要子查询结果集之后再count!!!
======done
*/
SELECT a.id, b.project_id,b.subproject_id ,b.subproject_name,COUNT(DISTINCT c.issue_id),COUNT(DISTINCT e.riskid_uuid)
FROM indexshow_project a
LEFT JOIN deliver_subproject b on b.project_id = a.id
LEFT JOIN deliver_question c on c.subproject_id = b.subproject_id
LEFT JOIN indexshow_proriskstatus d on d.subproject_id = b.subproject_id
INNER JOIN indexshow_risktotal e on e.riskid_uuid = d.riskid_id
WHERE
a.deliver_or_ops=1
AND a.is_valid=1
-- and a.id = 11234
AND b.is_valid=1
AND c.is_valid=1
and d.is_valid=1
and d.deliver_or_ops=1
and e.is_valid=1
and e.approved_status=0
GROUP BY a.id, b.subproject_id
ORDER BY a.id, b.subproject_id