项目经验：项目中分组row_number() over(partition by order by )总结

/**查询水质simple（不包含测站信息）项目，使用水功能区数据  查询每个测站最新插入的那条数据 */

     public Map<String, ItemSet> getSimpleItemSetList() {

           StringBuffer sb= new StringBuffer();

           sb.append( "select nmisp.*, pcp.*,misp.*,phncp.*,mooop.*,wbhp.*,opcp.*,bcp.*,aaacp.*,row_number() over(partition by nmisp.stcd order by nmisp.spt desc)cn from ZX_D_NMISP nmisp");

           sb.append( " left join ZX_D_PCP pcp on nmisp.stcd=pcp.stcd and nmisp.spt=pcp.spt");

           sb.append( " left join ZX_D_MISP misp on nmisp.stcd=misp.stcd and nmisp.spt=misp.spt");

           sb.append( " left join ZX_D_PHNCP phncp on nmisp.stcd=phncp.stcd and nmisp.spt=phncp.spt");

           sb.append( " left join ZX_D_MOOOP mooop on nmisp.stcd=mooop.stcd and nmisp.spt=mooop.spt");

           sb.append( " left join ZX_D_WBHP wbhp on nmisp.stcd=wbhp.stcd and nmisp.spt=wbhp.spt");

           sb.append( " left join ZX_D_OPCP opcp on nmisp.stcd=opcp.stcd and nmisp.spt=opcp.spt");

           sb.append( " left join ZX_D_BCP bcp on nmisp.stcd=bcp.stcd and nmisp.spt=bcp.spt");

           sb.append( " left join ZX_D_AAACP aaacp on nmisp.stcd=aaacp.stcd and nmisp.spt=aaacp.spt");

           sb.append( " where 1=1");

           Session session = template.getSessionFactory().getCurrentSession();

           SQLQuery sqlQuery = session.createSQLQuery(sb.toString());

           sqlQuery.addEntity( "pcp", ZxDPcp.class).addEntity("nmisp" ,ZxDNmisp.class).

                addEntity( "misp",ZxDMisp. class).addEntity( "phncp",ZxDPhncp. class).

                addEntity( "mooop",ZxDMooop. class).addEntity( "wbhp",ZxDWbhp. class).

                addEntity( "opcp", ZxDOpcp.class).addEntity("bcp" , ZxDBcp.class).addEntity("aaacp", ZxDAaacp.class);

           List<Object[]> list= sqlQuery.list();

           List<ItemSet> itemSetList= new ArrayList<ItemSet>();

            for (Object[] objs : list) {

                ItemSet itemSet= new ItemSet( null, (ZxDPcp)objs[0], (ZxDNmisp)objs[1],

                           (ZxDMisp)objs[2], (ZxDPhncp)objs[3], (ZxDOpcp)objs[6], (ZxDBcp)objs[7],

                           (ZxDAaacp)objs[8], (ZxDMooop)objs[4], (ZxDWbhp)objs[5], null);

                itemSetList.add(itemSet);

           }

           Map<String, ItemSet> itemSetAssessMap= new LinkedHashMap<String, ItemSet>();

            for(ItemSet itemSet:itemSetList){

                String stcd = itemSet.getNmisp().getId().getStcd();

                 if(itemSetAssessMap.get(stcd)== null){

                     itemSetAssessMap.put(stcd, itemSet);

                }

           }

//         List<ItemSet> itemSetList1=new ArrayList<ItemSet>();

//         Set<Map.Entry<String, ItemSet>> entrySet=itemSetAssessMap.entrySet();

//         for (Map.Entry<String, ItemSet> entry : entrySet) {

//              itemSetList1.add(entry.getValue());

//         }

           

            return itemSetAssessMap;

     }