oracle 中复杂sql处理

目前工作被分配了一个关于sql的工作，正好好好锻炼一下sql的能力

合同审核

需求

处理思路

首先分析一下功能需要的什么表，需要一个历史审批记录lstasks_his，用来展示每个人完成的表，需要一个共享中心表lsfssc用来联查共享中心。然后使用group函数对于共享中心分组，然后根据人员分组，然后根据日期分组。

处理方法

lstasks_his表中的LSTASKS_FSSC字段是共享中心表中的id，LSTASKS_DJLX字段是单据类型字段，LSTASKS_GNID字段是审批环节，LSTASKS_WCSJ字段是单据审批完成时间，LSTASKS_LOCKER字段是审批人。
lsfssc表中的LSFSSC_ID字段是主表id，LSFSSC_MC_CHS共享中心名称。

select LSFSSC_MC_CHS as 共享中心,
       '合规数字部' as 部门,
       LSTASKS_LOCKER as 人员,
       to_char(LSTASKS_WCSJ, 'yyyymmdd') as 日期,
       count(to_char(LSTASKS_WCSJ, 'yyyymmdd')) as 数量
  from lstasks_his
  left join lsfssc
    on lstasks_his.LSTASKS_FSSC = LSFSSC_ID
 where LSTASKS_DJLX = 'QTBZ'
   and LSTASKS_GNID = 'JZHTSH'
 group by LSFSSC_MC_CHS, LSTASKS_LOCKER, to_char(LSTASKS_WCSJ, 'yyyymmdd')
 order by to_char(LSTASKS_WCSJ, 'yyyymmdd')

首先对于共享中心进行分组，分组的结果就是各个共享中心审批的内容，然后对于人员分组就是每个人审批得内容，然后对于天数分组，就是每个共享中心每个人每天的审批的内容。

合同审核意见分布

需求

处理思路

目前思路有两种第一种是竖行展示，另外一种是横行展示。竖行展示就是使用子查询，三种审批状态套层处理，横行展示判断审核状态然后union all处理。

处理方法

横行展示：

lstasks_his表中的LSTASKS_FSSC字段是共享中心表中的id，LSTASKS_DJLX字段是单据类型字段，LSTASKS_GNID字段是审批环节，LSTASKS_WCSJ字段是单据审批完成时间，LSTASKS_LOCKER字段是审批人。

bfcontract表是合同信息表，id字段是代表主键，code字段是表示合同编号。

BFCONTRACTHEADEXT表是合同子表，EXTVARCHAR19字段展示具体合同审批信息，PARENTID字段是bfcontract的id字段。

BZZXQTBZ表用于合同审核的单据，本身是用来将合同变成一种单据来进行审核，QTBZ_SZ1字段表示合同金额，QTBZ_GXM1字段表示合同金额。

select bfb.NAME_CHS as 二级单位,bfa.NAME_CHS as 单位,A.acount as 合同数量,A.Asum as 合同金额,A.ares as 审批状态 from     
(select lstasks_his.LSTASKS_DWBH,
      count(BFCONTRACTHEADEXT.EXTVARCHAR19) as acount,
       sum(BZZXQTBZ.QTBZ_SZ1) as asum,
       '审核通过' as ares
  from lstasks_his
  LEFT JOIN BZZXQTBZ
    ON lstasks_his.LSTASKS_DJBH = QTBZ_DJBH
  left join bfcontract
    on bfcontract.code = BZZXQTBZ.QTBZ_GXM1
  left join BFCONTRACTHEADEXT
    on BFCONTRACTHEADEXT.PARENTID = bfcontract.id
 where lstasks_his.LSTASKS_DJLX = 'QTBZ'
   and lstasks_his.LSTASKS_GNID = 'JZHTSH'
   and EXTVARCHAR19 = '审核通过'
 group by lstasks_his.LSTASKS_DWBH) A
left join BFADMINORGANIZATION bfa
 on bfa.code = A.LSTASKS_DWBH
 left join BFADMINORGANIZATION bfb
 on  SUBSTR(bfa.TREEINFO_PATH, 0, 12)  = bfb.TREEINFO_PATH
 
union all  
 
select bfb.NAME_CHS as 二级单位,bfa.NAME_CHS as 单位,A.acount as 合同数量,A.Asum as 合同金额,A.ares as 审批状态 from     
(select lstasks_his.LSTASKS_DWBH,
      count(BFCONTRACTHEADEXT.EXTVARCHAR19) as acount,
       sum(BZZXQTBZ.QTBZ_SZ1) as asum,
       '保留通过' as ares
  from lstasks_his
  LEFT JOIN BZZXQTBZ
    ON lstasks_his.LSTASKS_DJBH = QTBZ_DJBH
  left join bfcontract
    on bfcontract.code = BZZXQTBZ.QTBZ_GXM1
  left join BFCONTRACTHEADEXT
    on BFCONTRACTHEADEXT.PARENTID = bfcontract.id
 where lstasks_his.LSTASKS_DJLX = 'QTBZ'
   and lstasks_his.LSTASKS_GNID = 'JZHTSH'
   and EXTVARCHAR19 = '保留通过'
 group by lstasks_his.LSTASKS_DWBH) A
left join BFADMINORGANIZATION bfa
 on bfa.code = A.LSTASKS_DWBH
 left join BFADMINORGANIZATION bfb
 on  SUBSTR(bfa.TREEINFO_PATH, 0, 12)  = bfb.TREEINFO_PATH
 
union all  
 
select bfb.NAME_CHS as 二级单位,bfa.NAME_CHS as 单位,A.acount as 合同数量,A.Asum as 合同金额,A.ares as 审批状态 from     
(select lstasks_his.LSTASKS_DWBH,
      count(BFCONTRACTHEADEXT.EXTVARCHAR19) as acount,
       sum(BZZXQTBZ.QTBZ_SZ1) as asum,
       '审核暂停' as ares
  from lstasks_his
  LEFT JOIN BZZXQTBZ
    ON lstasks_his.LSTASKS_DJBH = QTBZ_DJBH
  left join bfcontract
    on bfcontract.code = BZZXQTBZ.QTBZ_GXM1
  left join BFCONTRACTHEADEXT
    on BFCONTRACTHEADEXT.PARENTID = bfcontract.id
 where lstasks_his.LSTASKS_DJLX = 'QTBZ'
   and lstasks_his.LSTASKS_GNID = 'JZHTSH'
   and EXTVARCHAR19 = '审核暂停'
 group by lstasks_his.LSTASKS_DWBH) A
left join BFADMINORGANIZATION bfa
 on bfa.code = A.LSTASKS_DWBH
 left join BFADMINORGANIZATION bfb
 on  SUBSTR(bfa.TREEINFO_PATH, 0, 12)  = bfb.TREEINFO_PATH 
 
union all

select bfb.NAME_CHS as 二级单位,bfa.NAME_CHS as 单位,A.acount as 合同数量,A.Asum as 合同金额,A.ares as 审批状态 from     
(select lstasks_his.LSTASKS_DWBH,
      count(BFCONTRACTHEADEXT.EXTVARCHAR19) as acount,
       sum(BZZXQTBZ.QTBZ_SZ1) as asum,
       '总数' as ares
  from lstasks_his
  LEFT JOIN BZZXQTBZ
    ON lstasks_his.LSTASKS_DJBH = QTBZ_DJBH
  left join bfcontract
    on bfcontract.code = BZZXQTBZ.QTBZ_GXM1
  left join BFCONTRACTHEADEXT
    on BFCONTRACTHEADEXT.PARENTID = bfcontract.id
 where lstasks_his.LSTASKS_DJLX = 'QTBZ'
   and lstasks_his.LSTASKS_GNID = 'JZHTSH'
   and (EXTVARCHAR19 = '审核暂停' or EXTVARCHAR19 = '保留通过'or EXTVARCHAR19 = '审核通过')
 group by lstasks_his.LSTASKS_DWBH) A
left join BFADMINORGANIZATION bfa
 on bfa.code = A.LSTASKS_DWBH
 left join BFADMINORGANIZATION bfb
 on  SUBSTR(bfa.TREEINFO_PATH, 0, 12)  = bfb.TREEINFO_PATH