SELECT火FROM (SELECT Of2.SPATH ,vdf.FILENAME,vate.RSYS_ID,of2.DUPTIME,vate.RBUS-REO_ID ,Vs.SNAME,of2.idoctype,vdf.FILEID,vg.scoDE,ROW_NUMBER() OVER (PARTITION BY of2.doctype,vs.iid RDER BY of2.DUPTIME desc) nFROMVRM_ANALYSIS TASK vat
LEFTJOIN VRM_ANALYSIS_TASK_EXT vate ON vat.IID=vate.IITEMID
LEFTJOIN VPM_DELANDACC_FILEVdf ON Vdf.IITEMID=vat.iid
LEFTJOINOBJ_FILEINFOof2 0N vdf.fileid=of2.iidLEFTJOINVPM_SYSTEMVS ONVS.IID=vate.RSYS_IDLEFT JOIN vrm_businessreq vq ON vq.IID= vate.RBUS_REQ_IDWHERE vq.IID = #{iid) AND (vate.rsysid IS NO NULL o vatersys_id !=0) AND (of2.idoctype='33' 0R of2.idoctype='30))aaWHERE aa.rn=1
这是一条带有子查询和多个连接的Oracle SQL查询语句。下面是对查询语句的解释:
首先,查询从以下表中获取数据:
VRM_ANALYSIS_TASK(TASK表)
VRM_ANALYSIS_TASK_EXT(vate表)
VPM_DELANDACC_FILE(vdf表)
OBJ_FILEINFO(of2表)
VPM_SYSTEM(VS表)
VRM_BUSINESSREQ(vq表)
使用LEFT JOIN连接了这些表,关联条件如下:
vat.IID = vate.IITEMID
vdf.IITEMID = vat.IID
vdf.FILEID = of2.IID
VS.IID = vate.RSYS_ID
vq.IID = vate.RBUS_REQ_ID
WHERE子句中的条件包括:
vq.IID = #{iid}:vq.IID等于给定的参数#{iid}
(vate.RSYS_ID IS NOT NULL OR vate.RSYS_ID != 0):vate.RSYS_ID不为空或不等于0
(of2.IDOCTYPE = '33' OR of2.IDOCTYPE = '30'):of2.IDOCTYPE等于'33'或'30'
最后,使用ROW_NUMBER()函数对结果进行分组和排序,并将结果集限制为每个分组中的第一行。