数据有内在逻辑的表sql查询

来源：互联网发布：淘宝流量突然下降了编辑：程序博客网时间：2024/06/15 20:44

具体实例如下：

其中xe_dkzhxxb 是贷款表(小额贷表)，T_CODE_UNIT表是机构编码表，T_DHBL_M_GGXX是高管信息表。

要求：查询银行高管任职期间的相关贷款详情。

            select cv.*,cx.bled from(select sum(case
                when cast(xb.jkr As Date) >= gg.RZKSSJ and
                     cast(xb.jkr As Date) <= gg.RZJSSJ then
                 xb.dkye
              end) zrdkye,
          sum(case
                when cast(xb.jkr As Date) >= gg.RZKSSJ and
                     cast(xb.jkr As Date) <= gg.RZJSSJ and
                     xb.fljg in (2, 3, 4, 5) then
                 xb.dkye
              end) blzrdkye,
          gg.xm,
          gg.id
     from (select * from xe.xe_dkzhxxb exb where exb.dkjgdm in
     (select unit.code from T_CODE_UNIT unit start with unit.code='1101072000000' connect by PRIOR unit.code =unit.PCODE)) xb,
          (select w.* from T_DHBL_M_GGXX w where w.GRZW in (01,03) and w.ssdw='1101072000000') gg
    group by gg.XM, gg.id) cv,
    (select sum(tm.BLED) as BLED, ggxx.XM, ggxx.id
     from (
    select tz.*,xb.*
            from T_DHBL_M_BLTZ tz, xe.xe_dkzhxxb xb
            where tz.id = xb.id(+) and xb.dkjgdm
            in (select unit.code from T_CODE_UNIT unit start with unit.code='1101072000000' connect by PRIOR unit.code =unit.PCODE) ) tm,
             (select w.* from T_DHBL_M_GGXX w where w.GRZW in (01,03) and w.ssdw='1101072000000') ggxx
     where cast(tm.jkr As Date) >= ggxx.RZKSSJ
      and cast(tm.jkr As Date) <= ggxx.RZJSSJ
     group by ggxx.XM, ggxx.id
       ) cx
   where cv.id = cx.id(+);

在标注为红色的sql语句中没有使用字段链接，两个视图像是互不干涉的放在了一个sql语句中。视图xb与视图gg并没有相关字段能够联系，但是两个视图中的数据有内在的相关联系,具体是"认定开始时间"——RZKSSJ 和"认定结束时间" ——RZJSSJ，使两个表在这方面存在关联。所以可以这样写sql(红色的部分)。

另外注意oracle的sql的函数：

            sum(casewhen cast(xb.jkr As Date) >=gg.RZKSSJ and
                                      cast(xb.jkr As Date) <=gg.RZJSSJ then
                                       xb.dkye
                                       end) zrdkye   (这是借款日在一段时间范围内的总贷款余额)

case when ……and……then

0 0