select pe.plm_oid,
rm.plm_m_id,
pe.plm_createtime,
pe.plm_newname,
pe.plm_pdwgdate,
pe.plm_ppdwgdate,
pe.plm_pdwgcode,
pe.plm_ppdwgcode,
pe.plm_pdtdesign,
pe.plm_dpdtdesign,
pe.plm_pdwgname,
pe.plm_cxsj,
pe.plm_tzsj,
f.plm_oid,
w.plm_completeddate
from plm_cus_PROJECTECHFILE pe
left join PLM_PSM_FILE f
on pe.PLM_OID = f.PLM_ITEMOID, PLM_PSM_ITEMMASTER_REVISION rm,
PLM_BPMv_WORKITEM w, plm_bpmv_r_group_data_ins i,
plm_cus_r_XMHXMJSWJ x
where i.plm_bosubid = 1
and w.plm_name like '%批准'
and pe.plm_xmjswjfl = '设计任务书-电气'
and i.plm_PROCESSoid = w.plm_processinstanceid
and i.plm_bomoid = rm.plm_m_oid
and rm.plm_m_lastrevision = rm.plm_r_revision
and pe.PLM_ITERATION = rm.plm_r_lastiteration
and rm.plm_r_oid = pe.plm_revisionoid
and x.plm_rightobj = rm.plm_m_oid
and x.plm_leftobj = '917F410F954446F59DD3DB2F5D9B3247'
order by rm.plm_m_id
以上的代码,我发现PLM_BPMv_WORKITEM w这张表里会有多行记录,我希望返回PLM_BPMv_WORKITEM w里w.plm_completeddate
是最后的一条记录,请教如何改写?
解决方案 »
- linux中的数据库列表问题:File "/etc/oratab" is not accessible.
- 求sql:得到的是每天a字段最大值和最大值的id、最小值值和最小值的id
- oracle多字段检索问题
- 触发器变量
- Oracle9i的develop版的occi接口谁有啊?
- 一个困惑我好多天, 关于事务的问题,
- 用for update语句后,执行查询报错:fetch out of sequence(oracle数据库)
- oracle的重装问题
- 如何用asp连接数据库呢?
- 100分求解几个概念!。。。新手问题
- 为什么不能到处远程数据库文件
- sql/plus用sys as sysdba一登录完成就自动关闭窗口!
from
PLM_BPMv_WORKITEM
可能是我没表述清楚。
如果不关联PLM_BPMv_WORKITEM w,查出的记录都是不重复的记录,
但关联上PLM_BPMv_WORKITEM w后,有一些记录就有重复项了,区别只是plm_completeddate不一样。
我想把这些重复项里找一条最新的记录,不是重复的记录还要正常输出。就是PLM_BPMv_WORKITEM w经过条件
i.plm_PROCESSoid = w.plm_processinstanceid
w.plm_name like '%批准'后有多条记录。可我又与其他表联表了,这里如何处理?
我才学没几天,希望能给出完整参考,谢谢
i.plm_PROCESSoid = w.plm_processinstanceid
PLM_BPMv_WORKITEM 的plm_processinstanceid
唯一不?
over(partition by plm_processinstanceid order by idplm_completeddate desc) rn
from PLM_BPMv_WORKITEM plm) where rn=1
取每个plm_processinstanceid中最大的idplm_completeddate
(SELECT *
FROM PLM_BPMv_WORKITEM
WHERE (plm_processinstanceid,plm_completeddate) IN
(SELECT plm_processinstanceid,MAX(plm_completeddate) FROM PLM_BPMv_WORKITEM
GROUP BY plm_processinstanceid)) w
例如:select * from PLM_BPMv_WORKITEM where plm_processinstanceid in (select plm_processinstanceid,max(dplm_completeddate) from PLM_BPMv_WORKITEM group by plm_processinstanceid )