select a.EmpID,a.cowadt as CowaDate, (select b.ID from OA_LogisCowa b where b.cowatype='上午上班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as MODID, (select b.oacomment from OA_LogisCowa b where b.cowatype='上午上班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as MODCheck, (select b.ID from OA_LogisCowa b where b.cowatype='上午下班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as MOFDID, (select b.oacomment from OA_LogisCowa b where b.cowatype='上午下班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as MOFDCheck, (select b.ID from OA_LogisCowa b where b.cowatype='下午上班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as AODID, (select b.oacomment from OA_LogisCowa b where b.cowatype='下午上班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as AODCheck, (select b.ID from OA_LogisCowa b where b.cowatype='下午下班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as AOFDID, (select b.oacomment from OA_LogisCowa b where b.cowatype='下午下班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as AOFDCheck from OA_LogisCowa a where 1=1然后在程序中截取字符串.
select a.EmpID,a.cowadt as CowaDate, decode(b.cowatype,'上午上班',b.ID) as MODID, substr(decode(b.cowatype,'上午上班',b.comment),1,12)|| '...' as MODCheck,
decode(b.cowatype,'上午下班',b.ID) as MOFDID, substr(decode(b.cowatype,'上午下班',b.comment),1,12)|| '...' as MOFDCheck,
decode(b.cowatype,'下午上班',b.ID) as AODID, substr(decode(b.cowatype,'下午上班',b.comment),1,12)|| '...' as AODCheck,
decode(b.cowatype,'下午下班',b.ID) as AOFDID, substr(decode(b.cowatype,'下午下班',b.comment),1,12)|| '...' as AOFDCheck
from OA_LogisCowa a ,OA_LogisCowa b where a.cowadt=b.cowadt(+) and a.EmpID=b.EmpID(+)
(select b.ID from OA_LogisCowa b where b.cowatype='上午上班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as MODID,
(select b.oacomment from OA_LogisCowa b where b.cowatype='上午上班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as MODCheck,
(select b.ID from OA_LogisCowa b where b.cowatype='上午下班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as MOFDID,
(select b.oacomment from OA_LogisCowa b where b.cowatype='上午下班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as MOFDCheck,
(select b.ID from OA_LogisCowa b where b.cowatype='下午上班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as AODID,
(select b.oacomment from OA_LogisCowa b where b.cowatype='下午上班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as AODCheck,
(select b.ID from OA_LogisCowa b where b.cowatype='下午下班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as AOFDID,
(select b.oacomment from OA_LogisCowa b where b.cowatype='下午下班' and a.cowadt=b.cowadt and b.EmpID=a.EmpID) as AOFDCheck
from OA_LogisCowa a where 1=1然后在程序中截取字符串.
decode(b.cowatype,'上午上班',b.ID) as MODID,
substr(decode(b.cowatype,'上午上班',b.comment),1,12)|| '...' as MODCheck,
decode(b.cowatype,'上午下班',b.ID) as MOFDID,
substr(decode(b.cowatype,'上午下班',b.comment),1,12)|| '...' as MOFDCheck,
decode(b.cowatype,'下午上班',b.ID) as AODID,
substr(decode(b.cowatype,'下午上班',b.comment),1,12)|| '...' as AODCheck,
decode(b.cowatype,'下午下班',b.ID) as AOFDID,
substr(decode(b.cowatype,'下午下班',b.comment),1,12)|| '...' as AOFDCheck
from OA_LogisCowa a ,OA_LogisCowa b
where a.cowadt=b.cowadt(+) and a.EmpID=b.EmpID(+)