一个值得纪念的查询
出来工作了,两个月之前写的一条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