Oracle数据库查询某个字段的映射时,在sql语句中不需要连接 自动映射的方法-- case when

时间:2022-09-30 15:03:35

主题

  Oracle数据库查询某个字段的映射时,在sql语句中不需要连接  自动映射的方法-- case  when

原因   

  项目已经是很久很久的项目,对于某些表的字段属性和字段值  都已经模糊,根据页面状态来对数据库查出来的数据进行SQL  映射

代码如下

SELECT T.ORDER_SEQ as 销售订单号,
T.PAYINFO_SEQ as 查款编号,
N.PRTN_NAME as 代理商名称,
TO_CHAR(TO_DATE(T.CREATE_DATE,'YY/MM/DD HH24:MI:SS'),'YY/MM/DD HH24:MI:SS') as 交易时间, 
case when  P.ORDER_STAT = '11' then '待卡部审核'
     when  P.ORDER_STAT = '10' then '待订单明细录入'
     when  P.ORDER_STAT = '14' then '审核不通过'
     when  P.ORDER_STAT = '40' then '订单作废'  
     when  P.ORDER_STAT = '15' then '卡部审核不通过'
     when  P.ORDER_STAT = '17' then '待财务审核'
     when  P.ORDER_STAT = '18' then '财务审核不通过'
     when  P.ORDER_STAT = '12' then '待下单'
     when  P.ORDER_STAT = '21' then '作废'
       when  P.ORDER_STAT = '60' then '审核成功'
else P.ORDER_STAT end 查款订单状态,
case 
     when  T.ORDER_STAT = '10' then '待订单明细录入'
     when  T.ORDER_STAT = '11' then '待审核'
     when  T.ORDER_STAT = '14' then '审核不通过'  
     when  T.ORDER_STAT = '40' then '订单作废'
     when  T.ORDER_STAT = '60' then '审核成功'
else T.ORDER_STAT end 销售订单状态,
case 
     when  T.OPER_STAT = 'S0A' then '未发卡'
     when  T.OPER_STAT = 'S0B' then '已申请发卡'
     when  T.OPER_STAT = 'S0C' then '发卡成功'  
     when  T.OPER_STAT = 'S0F' then '发卡失败'
else T.ORDER_STAT end 发卡状态,
(SELECT S.DICT_NAME FROM T_SYM_DICT S WHERE S.DICT_TYPEID = 'PAY_TYPE' AND S.DICT_ID=P.PAY_TYPE) as 支付方式,
(SELECT S.DICT_NAME FROM T_SYM_DICT S WHERE S.DICT_TYPEID = 'SCD_SELLTYPE' AND S.DICT_ID=T.SELL_TYPE) as 销售类型,
P.STAFF_ID as 操作人,
T.VER_USER  as 审核人,
TO_CHAR(TO_DATE(T.APPLY_DATE,'YY/MM/DD HH24:MI:SS'),'YY/MM/DD HH24:MI:SS') as 实际发卡时间,  
(SELECT S.DICT_NAME FROM T_SYM_DICT S WHERE S.DICT_TYPEID = 'PNM_SIGNINGAREA' AND S.DICT_ID=P.N.SIGNING_AREA) as 签约区域,
T.MEMO as 备注 
 FROM T_SCD_ORDER T 
 left join  T_SCD_ORDER_PAYINFO P  on T.PAYINFO_SEQ = P.PAYINFO_SEQ
 left join  T_PNM_PARTNER N on P.PRTN_CODE=N.PRTN_CODE
  <where>
         <![CDATA[  to_date(CREATE_DATE,'yyyy-MM-dd hh24:mi:ss') >= to_date(#{CREATE_DATE_START},'yyyy-MM-dd hh24:mi:ss')]]>
        AND  <![CDATA[  to_date(CREATE_DATE,'yyyy-MM-dd hh24:mi:ss') <= to_date(#{CREATE_DATE_END},'yyyy-MM-dd hh24:mi:ss')]]>
        <if test="ORDERSEQ!=null  and ORDERSEQ.trim()!=''">
            AND T.ORDER_SEQ=#{ORDERSEQ}
        </if>
        <if test="PRTNID!=null and PRTNID.trim()!=''">
            AND N.PRTN_ID =#{PRTNID}
        </if>
        <if test="SELL_TYPE!=null and SELL_TYPE.trim()!=''">
            AND T.SELL_TYPE=#{SELL_TYPE}
        </if>
  </where>

 查询结果

  Oracle数据库查询某个字段的映射时,在sql语句中不需要连接 自动映射的方法-- case when