I will think of two ways, but feel bad,

The first:

 
String SQL="SELECT finishclass, finishtm from" + 
"(the select state finishclass, createtm finishtm, ROW_NUMBER () OVER (PARTITION BY the state ORDER BY createtm desc) rn" + 
"From PERSONAL_FINISHINFO_T where 1=1 and applyid=" + subsidyId + ") where an rn=1 "; 
List List=queryBySql (SQL); 
Iterator The iterator=list. The iterator (); 
Map The map=new HashMap<> (a); 
While (iterator. HasNext ()) {//10 data 
Map Next=iterator. Next (); 
Map. The put (next. Get (" finishclass ") + "", next, the get (" finishtm") + ""); 
} 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- separately, not in the same class, there are some omitted 
List MapList=subsidyApplyService 
ListSubsidyCustom (paramMap, nameSet); 
For (Map Map: mapList) {//5000 + data 
Map FinishInfo=subsidyApplyService. LastFinishInfo (map. Get (" SUBSIDYID "). The toString ()); 
If (finishInfo!=null) {
Map. Put (" PRELEXADATE finishInfo. Get (" 1 ")==null? "" : finishInfo. Get (" 1") + ""); 
Map. Put (" ONSITEAUDITDATE finishInfo. Get (" 4 ")==null? "" : finishInfo. Get (" 4") + ""); 
Map. Put (" FIRSTFINISHDATE finishInfo. Get (" 5 ")==null? "" : finishInfo. Get (" 5") + ""); 
Map. Put (" SECONDFINISHDATE finishInfo. Get (" 6 ")==null? "" : finishInfo. Get (" 6") + ""); 
Map. Put (" THIRDFINISHDATE finishInfo. Get (" 7 ")==null? "" : finishInfo. Get (" 7") + ""); 
Map. Put (" FINANCIALAUDIT finishInfo. Get (" 99 ")==null? "" : finishInfo. Get (" 99") + ""); 
} 
} 

The second:

 
String SQL="SELECT finishclass, finishtm from" + 
"(the select state finishclass, createtm finishtm, ROW_NUMBER () OVER (PARTITION BY the state ORDER BY createtm desc) rn" + 
"From PERSONAL_FINISHINFO_T) where rn=1"; 
List List=queryBySql (SQL); 
Iterator The iterator=list. The iterator (); 
Map The map=new HashMap<> (a); 
Map Map1=new HashMap<> (a); 
While (iterator. HasNext ()) {//article 20 + ten thousand data 
Map Next=iterator. Next (); 
Map. The put (next. Get (" finishclass ") + "", next, the get (" finishtm") + ""); 
If (map1. KeySet (). The contains (next) get (" ID ") + "")) {
Map IdMap=(Map) Map1. Get (" ID "); 
Map. PutAll (idMap); 
} 
Map1. Put (next. Get (" ID ") + "", map); 
} 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- separately, not in the same class, there are some omitted 
List MapList=subsidyApplyService 
ListSubsidyCustom (paramMap, nameSet); 
For (Map Map: mapList) {//5000 + data 
Map FinishInfo=(Map) Map1. Get (map. Get (" SUBSIDYID ") + ""); 
If (finishInfo!=null) {
Map. Put (" PRELEXADATE finishInfo. Get (" 1 ")==null? "" : finishInfo. Get (" 1") + ""); 
Map. Put (" ONSITEAUDITDATE finishInfo. Get (" 4 ")==null? "" : finishInfo. Get (" 4") + ""); 
Map. Put (" FIRSTFINISHDATE finishInfo. Get (" 5 ")==null? "" : finishInfo. Get (" 5") + ""); 
Map. Put (" SECONDFINISHDATE finishInfo. Get (" 6 ")==null? "" : finishInfo. Get (" 6") + ""); 
Map. Put (" THIRDFINISHDATE finishInfo. Get (" 7 ")==null? "" : finishInfo. Get (" 7") + ""); 
Map. Put (" FINANCIALAUDIT finishInfo. Get (" 99 ")==null? "" : finishInfo. Get (" 99") + ""); 
} 
} 

Directly in the master data that also tried joining together, slowly slowly, may be the stitching is not good, I use the subquery, every state is checked once again case the when,

CodePudding user response:

1. SQL optimization, on the table behind the ID and the where condition of all the keys to good indexing (lead it regardless whether reasonable, indexing, first listen to me, look at the query speed up no)
2. Check out so much, it must be to Insert, if it is to be inserted, don't use the Insert. Put it on the file, such as TXT, then use the import, the LOAD statements written for each database has its own LOAD, check can be found,
3. If it is returned to the other interface or a program to use, don't be this way, don't HTTP transmission byte stream, can consider to use the database or FTP or zip file, let the other side interface parsing the zip is ok,