需求：按照以下报表截图案例来编写sql
--------------准备工作,在查询分析器依次执行-----------------------
--1、执行sql
SELECT fid,'dsadsadsqadsadsadsadsadsadsa' as FLONGNUMBERSORT,FNUMBER,FLONGNUMBER INTO T_PM_PERMITEM_BK02 FROM T_pm_permitem
--2、执行sql
DELETE FROM T_PM_PERMITEM_BK02
--3、执行方言
INSERT INTO T_PM_PERMITEM_BK02(fid,FLONGNUMBERSORT,FNUMBER,FLONGNUMBER) SELECT fid,substr(FLONGNUMBER,1,instr(FLONGNUMBER,'!',1,2)-1) as FLONGNUMBERSORT,FNUMBER,FLONGNUMBER FROM T_pm_permitem
---------------报表1：给用户直接分配权限数据整理，依次执行-----------------------------
--1、给用户直接分配权限数据整理且备份到表t_pm_userorgperm_deal_bk
select
uop.FOWNER as fuserid ,uop.FORGID ,uop.FPERMTYPE,
p.FOPERATIONTYPE ,
pbk.flongnumbersort,
count(p.FOPERATIONTYPE) as FOPERATIONTYPENUM
INTO t_pm_userorgperm_deal_bk
from t_pm_userorgperm uop
INNER JOIN T_pm_permitem p on uop.FPERMITEMID = p.FID
INNER JOIN T_pm_user u on uop.FOWNER = u.FID
INNER JOIN T_org_baseunit bu on uop.FORGID = bu.FID
INNER JOIN T_PM_PERMITEM_BK02 pbk on p.FID = pbk.fid
where p.FISLEAF =1 group by p.FOPERATIONTYPE,uop.FOWNER,uop.FORGID,uop.FPERMTYPE,pbk.flongnumbersort;
--查询是否备份成功
SELECT * FROM t_pm_userorgperm_deal_bk;
--2、给用户直接分配权限数据整理后最终展示(授权类型：10代表有权限，20代表被禁用)
SELECT u.FNUMBER as 用户名,u.FNAME_L2 as 用户实名,u.FISDELETE as 用户是否删除, u.FFORBIDDEN as 用户是否被禁用 , bu.FNUMBER as 组织编码,bu.FNAME_l2 as 组织名称,uop.foperationtype as 操作类型,uop.flongnumbersort as 权限分类,
uop.FPERMTYPE as 授权类型, uop.foperationtypenum as 权限数量
FROM t_pm_userorgperm_deal_bk uop
INNER JOIN T_pm_user u on uop.Fuserid = u.FID
INNER JOIN T_org_baseunit bu on uop.FORGID = bu.FID
----------------------报表2：以下是通过角色分配权限数据整理，依次执行---------------------------
--1、给用户通过角色分配的权限数据整理且备份到表T_pm_userroleorg_deal_bk
SELECT
uro.FUSERID as fuserid ,uro.FORGID ,rp.FPERMTYPE,
p.FOPERATIONTYPE,
pbk.flongnumbersort,
count(p.FOPERATIONTYPE) as FOPERATIONTYPENUM
INTO T_pm_userroleorg_deal_bk
FROM T_pm_userroleorg uro
INNER JOIN T_pm_role r on uro.FROLEID = r.FID
INNER JOIN T_pm_roleperm rp on uro.FROLEID = rp.FROLEID
INNER JOIN T_pm_user u on uro.FUSERID = u.FID
INNER JOIN T_org_baseunit bu on uro.FORGID = bu.FID
INNER JOIN T_pm_permitem p on rp.FPERMITEMID = p.FID
INNER JOIN T_PM_PERMITEM_BK02 pbk on p.FID = pbk.fid
where p.FISLEAF =1 group by p.FOPERATIONTYPE,uro.FUSERID,uro.FORGID,rp.FPERMTYPE,pbk.flongnumbersort;
--查询是否备份成功
SELECT * FROM T_pm_userroleorg_deal_bk;
--2、给用户通过角色分配的权限数据整理后最终展示(授权类型：10代表有权限，20代表被禁用)
SELECT u.FNUMBER as 用户名,u.FNAME_L2 as 用户实名,u.FISDELETE as 用户是否删除, u.FFORBIDDEN as 用户是否被禁用 , bu.FNUMBER as 组织编码,bu.FNAME_l2 as 组织名称,uop.foperationtype as 操作类型,uop.flongnumbersort as 权限分类,
uop.FPERMTYPE as 授权类型, uop.foperationtypenum as 权限数量
FROM T_pm_userroleorg_deal_bk uop
INNER JOIN T_pm_user u on uop.Fuserid = u.FID
INNER JOIN T_org_baseunit bu on uop.FORGID = bu.FID
推荐阅读