请教怎么从其中一张表里只返回一个特定的记录

请问如何从其中一张表里只返回一个特定的记录？

SQL code

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
是最后的一条记录，请教如何改写？

[解决办法]

SQL code

select max(plm_completeddate) fromPLM_BPMv_WORKITEM
[解决办法]
把PLM_BPMv_WORKITEM w这个表，用一条SQL子查贸易来代替，相当于一个视图，在这里过滤掉重复数据即可。
SQL code
(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
[解决办法]
做一个子查询，
例如：
SQL code
select * from PLM_BPMv_WORKITEM where plm_processinstanceid in (select plm_processinstanceid,max(dplm_completeddate) from PLM_BPMv_WORKITEM group by plm_processinstanceid ) 

 

查看更多 下一篇