好的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)数/ 此巷道下的总库位数）

巴特西

好的Mysql 查询语句

最新文章

热门文章