最近项目中写的几个比较复杂的sql

1.这个是交叉表的应用, 用于统计分析报表

select entity_.*,
       entity_.field3 + entity_.field4 + entity_.field5 + entity_.field6 +
       entity_.field7 + entity_.field8 + entity_.field9 as sum_maintain,
       entity_.field1 + entity_.field2 + entity_.field3 + entity_.field4 +
       entity_.field5 + entity_.field6 + entity_.field7 + entity_.field8 +
       entity_.field9 + entity_.field10 as sum_all
  from (select result_.id,
               result_.name,
               sum(case result_.type_id
                     when 1 then -- 固定费用(一级)
                      amount
                      when 5 then -- 复印打印(二级)
                      amount
                      when 6 then -- 办公用品(二级)
                      amount
                      when 7 then -- 运费(二级)
                      amount
                      when 8 then -- 汇款手续费(二级)
                      amount
                      when 9 then -- 邮寄费(二级)
                      amount
                     else
                      0
                   end) field1, -- 固定费用,
               sum(case result_.type_id
                     when 2 then -- 差旅费(一级)
                      amount
                      when 10 then -- 通讯费(二级)
                      amount
                      when 11 then -- 业务招待费(二级)
                      amount
                      when 12 then -- 车辆使用费(二级)
                      amount
                      when 13 then -- 房租(二级)
                      amount
                      when 14 then -- 差旅车票(二级)
                      amount
                      when 15 then -- 住宿费(二级)
                      amount
                     else
                      0
                   end) field2, --  差旅费,
               sum(case result_.type_id
                     when 16 then
                      amount
                     else
                      0
                   end) field3, --  临聘人员奖励提成费,
               sum(case result_.type_id
                     when 17 then
                      amount
                     else
                      0
                   end) field4, --  促销礼品费,
               sum(case result_.type_id
                     when 18 then
                      amount
                     else
                      0
                   end) field5, -- 展示特陈费,
               sum(case result_.type_id
                     when 19 then
                      amount
                     else
                      0
                   end) field6, --  茶友会活动费,
               sum(case result_.type_id
                     when 20 then
                      amount
                     else
                      0
                   end) field7, --  新品进场费,
               sum(case result_.type_id
                     when 21 then
                      amount
                     else
                      0
                   end) field8, --  店员奖励,
               sum(case result_.type_id
                     when 22 then
                      amount
                     else
                      0
                   end) field9, --  终端客情费
               sum(case result_.type_id
                     when 4 then
                      amount
                     else
                      0
                   end) field10 --奖金
from (select detail_.*, s.groupname as name
                  from (select d.type_id,
                               d.amount,
                               get_salagroup_id(:salegroupid, d.employee_id) as id
                          from ch_process_form             pf,
                               ch_process_form_item_detail d
                         where d.form_id = pf.id
                           and (pf.process_type = :processType1 -- 拨付申请
                               or pf.process_type = :processType2) -- 报销申请
                           and pf.process_state != 4 -- 去掉被取消的
                           and (pf.process_state = :state or :state = -1)
                           and to_char(pf.create_date, 'yyyy-mm-dd hh24:mi:ss') >=
                               :start_
                           and to_char(pf.create_date, 'yyyy-mm-dd hh24:mi:ss') <=
                               :end_) detail_,
                       zx_dm_salegroup s
                 where detail_.id = s.salegroupid
                   and detail_.id != 0) result_ -- 0表示统计的detail的使用employee不在当前的区域
         group by result_.id, result_.name) entity_

2.这个是一个存储函数, 比较简单, 主要是oracle中connect by prior start with的用法

 create or replace function GET_SALAGROUP_ID1(topgroupid in number,
                                             accountid  in number)
-- 根据给定的账户id找到对应的最上级辖区id, 且该辖区id必须小于给定的topgroupid
 return number is
  Result number;
  cursor c1 is
    select min(s.salegroupid)
      from zx_dm_salegroup s
    connect by prior s.managegroupid = s.salegroupid -- 由子找父
           and s.salegroupid != topgroupid -- 给定向上找的限定条件
     start with s.salegroupid = (select es.salegroupid
                                   from zx_dm_employee_sale es,
                                        ch_account_employee ae,
                                        ch_account          a
                                  where es.employeeid = ae.employee_id
                                    and ae.main_id = a.main_id
                                    and a.id = accountid); -- 取得对应帐号的辖区id
begin
  open c1;
  fetch c1
    into result;
  close c1;
  return(Result);
end GET_SALAGROUP_ID1;

3.用一个字段做累加计算找余额

select remain_.code_id, --费用帐号
       remain_.type_id,
       remain_.type_name,
       current_.amount, -- 当前申请核销金额
       proposal_.amount as proposal_amount, --当前申请拨付金额
       payment_.amount + nvl(current_.amount, 0) as enable_amount, -- 可用核销余额
       remain_.remain + nvl(proposal_.amount, 0) as enable_remain, -- 可用费用余额
       remain_.remain as sum_amount -- 费用总额
  from (select ct.id as type_id,
               ct.type_name,
               max_.id,
               max_.code_id,
               cd.remain - nvl(freeze_.amount, 0) as remain
          from ch_code_deal cd,
               ch_charge_type ct,
               ch_code c,
               (select max(cd.id) as id, c.id as code_id
                  from ch_code c, ch_code_deal cd
                 where c.account_id =
                       (select a2.id
                          from ch_account a1, ch_account a2
                         where a1.id = :accountId
                           and a2.main_id = a1.main_id
                           and a2.account_type = 1)
                   and cd.code_id = c.id
                 group by c.id) max_, -- 从最后一条交易记录中取出帐号的费用余额
               (select cf.code_id, sum(cf.amount) as amount
                  from ch_code_freeze cf
                 where cf.freeze_flag = 1
                 group by cf.code_id) freeze_ -- 处理中(被冻结)的费用   
         where cd.id = max_.id
           and cd.code_id = c.id
           and c.type_id = ct.id
           and cd.code_id = freeze_.code_id(+)) remain_, -- 取出账户的费用余额           
       (select i.type_id, i.amount - nvl(certi_.amount, 0) as amount
          from ch_process_form_item i,
               (select i.type_id, sum(i.amount) amount
                  from ch_process_form_item i, ch_process_form f
                 where f.parent_id = :parentFormId
                   and i.form_id = f.id
                   and f.process_type = :processTypeId
                   and f.process_state != 4 --去掉被取消的
                 group by i.type_id) certi_ -- 已经核销的部分
         where i.form_id = :parentFormId
           and i.type_id = certi_.type_id(+)) payment_, -- 可用核销金额
       (select type_id, code_id, amount
          from ch_process_form_item
         where form_id = :proposalFormId) proposal_, -- 当前拨付申请
       (select type_id, code_id, amount
          from ch_process_form_item
         where form_id = :formId) current_ -- 当前核销申请
 where remain_.type_id = current_.type_id(+)
   and remain_.type_id = payment_.type_id(+)
   and remain_.type_id = proposal_.type_id(+)
   and payment_.amount + nvl(current_.amount, 0) > 0

4.union的使用

select form_.account_id,
       form_.account_name,
       ct.id type_id,
       ct.type_name,
       sum(expense_remain) expense_remain,
       sum(expense_freeze_amount) expense_freeze_amount,
       sum(expense_enable_amount) expense_enable_amount,
       sum(fund_remain) fund_remain,
       sum(fund_freeze_amount) fund_freeze_amount,
       sum(fund_enable_amount) fund_enable_amount
  from (select a.id as account_id,
               a.name as account_name,
               ae.employee_id,
               t.id type_id,
               remain_.remain as expense_remain,
               freeze_.amount as expense_freeze_amount,
               remain_.remain - nvl(freeze_.amount, 0) expense_enable_amount,
               null fund_remain,
               null fund_freeze_amount,
               null fund_enable_amount
          from ch_code c,
               ch_account a,
               ch_account_employee ae,
               ch_charge_type t,
               (select d.code_id, d.remain
                  from ch_code_deal d,
                       (select d.code_id, max(d.id) as id
                          from ch_code_deal d
                         group by d.code_id) last_
                 where d.id = last_.id) remain_, --余额
               (select f.code_id, sum(f.amount) as amount
                  from ch_code_freeze f, ch_account a
                 where f.account_id = a.id
                   and a.account_type = 1
                   and f.freeze_flag = 1 -- 费用冻结
                 group by f.code_id) freeze_ -- 费用账户金额
         where c.id = remain_.code_id(+)
           and c.id = freeze_.code_id(+)
           and c.account_id = a.id
           and a.account_type = 1
           and a.main_id = ae.main_id
           and c.type_id = t.id
           and remain_.remain is not null
        union
        select a.id as account_id,
               a.name as account_name,
               ae.employee_id,
               t.id type_id,
               null expense_remain,
               null expense_freeze_amount,
               null expense_enable_amount,
               remain_.remain fund_remain,
               freeze_.amount fund_freeze_amount,
               remain_.remain - nvl(freeze_.amount, 0) fund_enable_amount
          from ch_code c,
               ch_account a,
               ch_account_employee ae,
               ch_charge_type t,
               (select d.code_id, d.remain
                  from ch_code_deal d,
                       (select d.code_id, max(d.id) as id
                          from ch_code_deal d
                         group by d.code_id) last_
                 where d.id = last_.id) remain_,
               (select f.code_id, sum(f.amount) as amount
                  from ch_code_freeze f, ch_account a
                 where f.account_id = a.id
                   and a.account_type = 2
                   and f.freeze_flag = 3 -- 核销冻结
                 group by f.code_id) freeze_ -- 资金账户金额
         where c.id = remain_.code_id(+)
           and c.id = freeze_.code_id(+)
           and c.account_id = a.id
           and a.account_type = 2
           and a.main_id = ae.main_id
           and c.type_id = t.id
           and remain_.remain is not null) form_,
       ch_charge_type ct
 where ct.id = form_.type_id
   and form_.account_name like :accountName

4.这个是连接的使用(target中的左右连接比较复杂)

select source_.*, target_.target_enable_remain, target_.target_sum_amount
  from (select last_.code_id,
               last_.type_id,
               last_.type_name,
               current_.amount as amount, -- 申请金额
               auditing_.amount as auditing_amount, -- 在批金额
               nvl(last_.remain, 0) - nvl(paying_.amount, 0) as enable_remain -- 可用余额
          from (select cd.code_id, cd.remain, c.type_id, c.type_name
                  from ch_code_deal cd,
                       (select c.id, t.id type_id, t.type_name
                          from ch_code c, ch_account a, ch_charge_type t
                         where a.id = :sourceAccountId
                           and c.account_id = a.id
                           and c.enable_flag = 1
                           and c.type_id = t.id
                           and t.enable_flag = 1) c,
                       (select max(cd.id) as id
                          from ch_code_deal cd
                         group by cd.code_id) max_ -- 支出方费用帐号余额
                 where cd.id = max_.id
                   and cd.code_id = c.id) last_,
               (select cf.code_id, sum(cf.amount) amount
                  from ch_code_freeze cf
                 where cf.account_id = :sourceAccountId
                   and cf.process_type = :processType
                   and cf.form_id != :formId
                 group by cf.code_id) auditing_, --在批金额
               (select cf.code_id, sum(cf.amount) amount
                  from ch_code_freeze cf
                 where cf.account_id = :sourceAccountId
                   and cf.freeze_flag = 1 -- 在拨申请中
                 group by cf.code_id) paying_, --拨付申请金额
               (select i.code_id, i.amount
                  from ch_process_form_item i
                 where form_id = :formId) current_
         where last_.code_id = auditing_.code_id(+)
           and last_.code_id = paying_.code_id(+)
           and last_.code_id = current_.code_id(+)) source_, --支出帐号信息
       (select last_.code_id, last_.type_id,
               nvl(last_.remain, 0) - nvl(paying_.amount, 0) as target_enable_remain, -- 可用余额
               nvl(last_.remain, 0) as target_sum_amount -- 费用总额
          from (select c.id code_id, c.type_id, cd.remain
                  from ch_code_deal cd,
                       (select c.id, t.id type_id
                          from ch_code c, ch_account a, ch_charge_type t
                         where a.id = :targetAccountId
                           and c.account_id = a.id
                           and c.enable_flag = 1
                           and c.type_id = t.id
                           and t.enable_flag = 1) c,
                       (select max(cd.id) as id
                          from ch_code_deal cd
                         group by cd.code_id) max_
                 where cd.id = max_.id(+)
                   and cd.code_id(+) = c.id) last_,
               (select cf.code_id, sum(cf.amount) amount
                  from ch_code_freeze cf
                 where cf.account_id = :targetAccountId
                   and cf.freeze_flag = 1 -- 拨付申请中
                 group by cf.code_id) paying_ -- 拨付申请金额
         where last_.code_id = paying_.code_id(+)) target_ -- 接收帐号信息
 where source_.type_id = target_.type_id

5.这个也是union加聚合函数sum的使用, 用来将两类数据分组为两大类, 然后每一类是一列

select form_.main_id,
       e_.employeeid,
       e_.employeename,
       form_.type_id,
       ct.type_name,
       sum(expense_remain) expense_remain, -- 这里必须使用sum来聚合一下, 否则费用和资金的金额将不会合并, 记录集会增加一倍
       sum(expense_freeze_amount) expense_freeze_amount,
       sum(expense_enable_remain) expense_enable_remain,
       sum(fund_remain) fund_remain,
       sum(fund_freeze_amount) fund_freeze_amount,
       sum(fund_enable_remain) fund_enable_remain
  from (select ae.main_id,
               ae.employee_id,
               remain_.type_id,
               remain_.remain as expense_remain,
               freeze_.amount as expense_freeze_amount,
               remain_.remain - nvl(freeze_.amount, 0) expense_enable_remain,
               null fund_remain,
               null fund_freeze_amount,
               null fund_enable_remain
          from ch_account_employee ae,
               (select d.code_id, c.type_id, a.main_id, d.remain, max(d.id)
                  from ch_code_deal d, ch_account a, ch_code c
                 where d.code_id = c.id
                   and c.account_id = a.id
                   and a.account_type = 1
                 group by d.code_id, c.type_id, a.main_id, d.remain) remain_, --余额
               (select f.code_id, sum(f.amount) as amount
                  from ch_code_freeze f, ch_account a
                 where f.account_id = a.id
                   and a.account_type = 1
                   and f.freeze_flag = 1
                 group by f.code_id) freeze_ -- 待拨费用
         where remain_.code_id = freeze_.code_id(+)
           and remain_.main_id = ae.main_id
        union
        select ae.main_id,
               ae.employee_id,
               remain_.type_id,
               null expense_remain,
               null expense_freeze_amount,
               null expense_enable_remain,
               remain_.remain fund_remain,
               freeze_.amount fund_freeze_amount,
               remain_.remain - nvl(freeze_.amount, 0) fund_enable_remain
          from ch_account_employee ae,
               (select d.code_id, c.type_id, a.main_id, d.remain, max(d.id)
                  from ch_code_deal d, ch_account a, ch_code c
                 where d.code_id = c.id
                   and c.account_id = a.id
                   and a.account_type = 2
                 group by d.code_id, c.type_id, a.main_id, d.remain) remain_,
               (select f.code_id, sum(f.amount) as amount
                  from ch_code_freeze f, ch_account a
                 where f.account_id = a.id
                   and a.account_type = 2
                   and f.freeze_flag = 3 -- 核销冻结
                 group by f.code_id) freeze_ -- 资金账户金额
         where remain_.code_id = freeze_.code_id(+)
           and remain_.main_id = ae.main_id) form_,
       ch_charge_type ct,
       zx_dm_employee e_
 where ct.id = form_.type_id
   and e_.employeeid = form_.employee_id
   and e_.employeename like ?
 group by form_.main_id,
          e_.employeeid,
          e_.employeename,
          form_.type_id,
          ct.type_name