over(partition by c1.pmid,d1.type,e1.objid order by e1.objid ) pinum
先根据字段排序,pinum。在取第一条数据and p1.pinum=1
partition by
虽然也具有分组功能,但同时也具有其他的功能。
它属于oracle的分析用函数。
row_number() 顺序排序
select row_number() over(partition by deptid order by salary) my_rank ,deptid,USERID,salary from tsaler;
public List<Map> getInstitutionsrela(String companycode) { Map map = new HashMap(); String type= ""+ChangeLog.TYPE_CCZT.getEnumItemValue()+","+ChangeLog.TYPE_MBQY.getEnumItemValue()+","+ChangeLog.TYPE_ZXAP.getEnumItemValue()+","+ChangeLog.TYPE_DLJDR.getEnumItemValue()+"," + ""+ChangeLog.TYPE_XPJG.getEnumItemValue()+","+ChangeLog.TYPE_FLJG.getEnumItemValue()+","+ChangeLog.TYPE_SJJG.getEnumItemValue()+","+ChangeLog.TYPE_GWJG.getEnumItemValue()+"," + ""+ChangeLog.TYPE_TGR.getEnumItemValue()+","+ChangeLog.TYPE_XTJG.getEnumItemValue()+""; StringBuilder sql = new StringBuilder(); sql.append("select p.objid as productid, " + " p1.objid as instiid, " + " p.name as productname, " + " decode( p3.name,null,p1.name,p3.name ) instiname , " + " decode(p.isend,1,'已到期','未到期') as isendname, " + " nvl(p.nowamount,0) nowamount, " + " p4.name as typename " + "from pm_product p " + "inner join ( " + " select c1.pmid,d1.type,e1.objid,e1.name,e1.parentinstitution,row_number() over(partition by c1.pmid,d1.type,e1.objid order by e1.objid ) pinum " + " from("+Institutions.getQuerySql("objid","companycode","sourcedb","parentinstitution","name")+") e1 " + " left join ("+YsInstitutionsRela.getQuerySql("institutionsid","changelogid","removetag","type","sourcedb")+") d1 " + " on e1.objid = d1.institutionsid and e1.sourcedb = d1.sourcedb " + " left join ("+YsChangeLog.getQuerySql("pmid","objid","datastatus","sourcedb")+") c1 " + " on d1.changelogid = c1.objid and c1.sourcedb = d1.sourcedb and d1.removetag = 0 " + " where c1.datastatus = 0 " ); QueryUtils.build("=","e1.companycode",companycode,sql,map); QueryUtils.buildIn("d1.type",type.split(","),sql,map); sql.append(")p1 on p1.pmid = p.sourceid and p1.pinum=1 " + "left join sirm_entitysetting p3 on p3.sourceid = p1.objid and p3.value = p1.parentinstitution " + "and p3.sourceentity = 'PMYSINSTITUTIONSRELA'and p3.name = 'parentInstitution' " + "left join sirm_enum p4 " + "on p1.type=p4.value " + "and p4.catalog='PROJECT'and p4.type='insttype' " + "where p.removetag = 0 " + "and p.currentpool = 1 order by p.isend, p.nowamount desc "); IMetaDBQuery query = getMetaDBContext().createSqlQuery(sql.toString()); query.setParameters(map); List<Map> list = query.getResult(); return list; }