一个值得纪念的SQL查询语句-CSDN博客

本文链接：https://blog.csdn.net/yang_best/article/details/41119457

一个值得纪念的查询

出来工作了，两个月之前写的一条SQL语句，今天突然想特别写此博客纪念一下它。

这条SQL比较长，其实也很简单。把它贴出来，让自己以后老了可以目睹一下。union，时间处理，case when ,order by等用法都可以在这里有体现，也希望广大网友可以从中拿到自己想要的东西。高手莫喷，直接无视此博客！仅给想看的人看！

(select  to_char(m.posttingdate,'YYYY-MM-DD HH:MM') as dotime , m.oper_user as operator_name, m.m_mailcode,c.username as "所属客户", m.r_postname as "寄达国家", m.weight as "邮件重量",
'','','','','',  '收寄处理' as oper,'' from ipcs_tb_russia_mail m, ipcs_tb_hj_client c
 where m.m_mailcode = 'RC096003672CN'
   and m.u_userno = c.userid)
   union
   (
   select to_char(DM.Importdate,'YYYY-MM-DD HH:MM') as dotime , '',DM.m_Mailcode ,hc.username  ,'','','','','','','','条码申请' as oper,''
  from Ipcs_Dict_Mailcode DM, Ipcs_Tb_Hj_Client hc
 where DM.m_Mailcode = 'RC096003672CN'
   and DM.u_Userno = hc.userid
   )
   union
   (select
      to_char( RI.Interceptdate,'YYYY-MM-DD HH:MM')  as dotime, RI.INTERCEPT_OPER as operator_name,'','','','','',
        to_char(RI.Releasedate,'YYYY-MM-DD HH:MM') as "解除截件时间",
       RI.release_oper as "解除操作员",
        to_char(RI.Intercepteddate,'YYYY-MM-DD HH:MM') as "截件时间",
       RI.INTERCEPTED_OPER as "截件操作人",
(case  RI.STATUS when '0' then '取消截件'
when '1'then '截件申请'
  when '2' then '截件成功'
    else '无此邮件状态'  end )     as oper,''
  from IPCS_TB_RUSSIA_INTERCEPT RI
 WHERE RI.m_Mailcode = 'RC096003672CN')
 union
(select to_char(RJM.REJECTTIME,'YYYY-MM-DD HH:MM') as dotime, U.u_Realname as operator_name,'','','','',RC.CAUSE as "退件原因",'','','','', '退件处理' as oper,''
  from IPCS_TB_REJECTMAIL RJM, IPCS_TB_REJECTCAUSE RC, IPCS_TB_USER U
 WHERE RJM.m_Mailcode = 'RC096003672CN'
   and RJM.Cause = RC.Id
   and RJM.Oper_User = U.U_USERNAME)
  union
   (select to_char(MH.DELDATE,'YYYY-MM-DD HH:MM') as dotime , MH.Deloperator as operator_name,'','','','','','','','','','删件处理' as oper,'' from IPCS_TB_MAILHIS MH
 WHERE MH.M_MAILCODE = 'RC096003672CN')
 union
 (select to_char(createdate,'YYYY-MM-DD HH:MM') as dotime,'','','','','','','','','','','录入数据表' as oper,'' from ipcs_tb_russia_temp rt where rt.m_mailcode='RC096003672CN')
    union
    (select  to_char(createdate,'YYYY-MM-DD HH:MM') as dotime,'','','','','','','','','','','录入数据表' as oper,'' from ipcs_tb_russia_temphis rth where rth.m_mailcode='RC096003672CN')
    union
    (select to_char(mn.d_pubtime,'YYYY-MM-DD HH:MM') as dotime,
       U.U_REALNAME as operator_name,'','','','','','','','','','无数据邮件登记' as oper,
       (case mn.status
         when 0 then
          '待处理'
         when 1 then
          '已补录'
         when 2 then
          '已退件'
         else
          '无此状态'
       end) as "无数据的状态" from Ipcs_Tb_Mail_Nodata mn, IPCS_TB_USER U where mn.v_mailcode='RC096003672CN'and u.u_username=mn.v_opuser
  )
  order by dotime desc