About the recommended location Java front end and SQL statement after the combination

-----------------------------------------------------------------------------------

Select A1.id,a1.name,a1.sort,a1.type,A3.value-(a1.nums/a2.nums) *100 as Nums, A3.value,a1.nums,a2.nums,a1.packagenum
From (

Select swr.id,swr.name,swr.sort,srscm.area_id as Type,count (swl.id) as Nums,ifnull (sum (swl.package_num), 0) Packagenum
From Sys_warehouse_roadway SWR
Join Sys_warehouse_location SWL on swr.id = swl.roadway_id
and swl.status = 1 and swl.delete_status = 0 and swl.package_num > 0
Join sys_recommend_store_code_mapping srscm on srscm.area_id = swr.area_id
where Swr.status = 1 and swr.delete_status = 0 and swr.name= ' A10 '
GROUP by swr.id,swr.name,swr.sort,srscm.area_id
) A1,
(
Select swr.id,swr.name,swr.sort,srscm.area_id as Type,count (1) as Nums
From Sys_warehouse_roadway SWR
Left join sys_warehouse_location swl on swr.id = swl.roadway_id and swl.status = 1 and swl.delete_status = 0
Left join sys_recommend_store_code_mapping srscm on srscm.area_id = swr.area_id
where Swr.status = 1 and swr.delete_status = 0 and swr.name= ' A10 '
GROUP by swr.id,swr.name,swr.sort,srscm.area_id
) A2,
(
SELECT * from Sys_key_value skv where Skv.key = ' recommandstorepercent '
) A3
where a1.id = A2.id
-- -------------------------------------------------------------------------------------

Select Swr.id,swr.name,swr.sort as type,count (swl.id) as Nums, ifnull (sum (swl.package_num), 0) Packagenum
From Sys_warehouse_roadway SWR
Left join sys_warehouse_location swl on swr.id = swl.roadway_id
and swl.status = 1 and swl.delete_status = 0 and swl.package_num > 0
where Swr.status = 1 and swr.delete_status = 0
GROUP by Swr.id,swr.name,swr.sort

Select Swr.id,swr.name,swr.sort,count (1) as Nums,count (swl.id) as Nums2
From Sys_warehouse_roadway SWR
Left join sys_warehouse_location swl on swr.id = swl.roadway_id and swl.status = 1 and swl.delete_status = 0
where Swr.status = 1 and swr.delete_status = 0
GROUP by Swr.id,swr.name,swr.sort

----Roadway is selected by the decision of the country (parcel type).
----Description: 1. First, count the roadway under 80% in the roadway under the package inventory rate (swl.package_num>0 query the number of parcels (swl.package_num>0) under this roadway/the total number of positions under this roadway)

---Select a location: (in the Location table, the location of the least parcel roadway is checked according to the number of location parcels)

Select Swl.id,swl.name from Sys_warehouse_location SWL

where swl.roadway_id = #{roadwayid} and Swl.status = 1

ORDER by Swl.package_num
LIMIT 1

-------------------------------------------------------------------------the wording in Java

Filter the tunnel is saturated
list<roadwayinforecommenddto> baohelist = Roadwayinfolist.stream (). Filter (A-a.getnums () > 0). Collect (Collectors.tolist ());

Record log
Logdto.setrequest ("A.getnums () > 0, filter out the already saturated roadway");
Logdto.setresponse ("Filter already saturated roadway" + Jsonobject.tojson (baohelist));
Recommendstorecodemanager.insertrecommendstorecodelog (logdto);

list<roadwayinforecommenddto> serchlist = new arraylist<> ();

if (Collectionutils.isnotempty (baohelist)) {
Not saturated
Listutils.sort (baohelist, "Nums", true);

Listutils.sort (baohelist, "sort", true);

Serchlist = baohelist;
} else {
is saturated
Listutils.sort (roadwayinfolist, "Packagenum", true);

Serchlist = roadwayinfolist;
}

Long Roadwayid = serchlist.get (0). GetId ();

Get Start time
StartTime = System.currenttimemillis ();

Locationinforecommenddto randomlocationinfo = recommenddao.getrandomlocation (Roadwayid, 0);

Get End time
EndTime = System.currenttimemillis ();
RunTime = (endtime-starttime) + "MS";
Logdto.setrequest ("First order to obtain any location query time according to the roadway");
Logdto.setresponse (RunTime);
Recommendstorecodemanager.insertrecommendstorecodelog (logdto);

if (Randomlocationinfo = = null) {
Message = "by roadway" + Roadwayid + "not acquired to any location";
Logdto.setrequest ("{\" roadwayid\ ": \" "+ Roadwayid +" \ "}");
Logdto.setresponse (message);
Recommendstorecodemanager.insertrecommendstorecodelog (logdto);

Response.setsucess (FALSE);
Response.setmessage (message);
Response.setrecommendstorecode ("");
return response;
}

Record log
Logdto.setrequest ("{\" roadwayid\ ": \" "+ Roadwayid +" \ "}");
Logdto.setresponse ("according to the roadway, get location information" + Jsonobject.tojson (randomlocationinfo));
Recommendstorecodemanager.insertrecommendstorecodelog (logdto);

Response.setsucess (TRUE);
Response.setrecommendstorecode (Randomlocationinfo.getname ());
Response.setmessage ("");
return response;

-----------------------------------------------------------------

About the recommended location Java front end and SQL statement after the combination