mysql准确查询语句_好的Mysql 查询语句

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

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

-- --1.求出此巷道 有包裹的库位数A

-- --2.求出此巷道 所有的库位数B

-- --3.求出此巷道 库位使用率 (A/B)*100%

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

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 * from sys_warehouse_roadway swr limit 100 -- --巷道是通过(包裹类型 国家等决定选取那个巷道). -- --说明：1.首先统计巷道下包裹库存率在80%以下的巷道 -- --(swl.package_num>0 查询此巷道下的有包裹的库位(swl.package_num>0)数/ 此巷道下的总库位数)