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,\n" +
" p1.objid as instiid,\n" +
" p.name as productname,\n" +
" decode( p3.name,null,p1.name,p3.name ) instiname , \n" +
" decode(p.isend,1,'已到期','未到期') as isendname,\n" +
" nvl(p.nowamount,0) nowamount,\n" +
" p4.name as typename \n" +
"from pm_product p\n" +
"inner join (\n" +
" 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\n" +
" left join ("+YsInstitutionsRela.getQuerySql("institutionsid","changelogid","removetag","type","sourcedb")+") d1\n" +
" on e1.objid = d1.institutionsid and e1.sourcedb = d1.sourcedb\n" +
" left join ("+YsChangeLog.getQuerySql("pmid","objid","datastatus","sourcedb")+") c1 \n" +
" on d1.changelogid = c1.objid and c1.sourcedb = d1.sourcedb and d1.removetag = 0\n" +
" where c1.datastatus = 0 \n" );
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\n" +
"left join sirm_entitysetting p3 on p3.sourceid = p1.objid and p3.value = p1.parentinstitution " +
"and p3.sourceentity = 'PMYSINSTITUTIONSRELA'and p3.name = 'parentInstitution' \n" +
"left join sirm_enum p4 \n" +
"on p1.type=p4.value \n" +
"and p4.catalog='PROJECT'and p4.type='insttype'\n" +
"where p.removetag = 0 \n" +
"and p.currentpool = 1 order by p.isend, p.nowamount desc\n");
IMetaDBQuery query = getMetaDBContext().createSqlQuery(sql.toString());
query.setParameters(map);
List<Map> list = query.getResult();
return list;
}