立库常用的查询语句

查询有效库位

#货位信息
#成品库区 原材料库区  #原材料巷道1  成品1号巷道
set @kuqu:="原材料库区";
set @xiangdao:= "原材料巷道4" ;
select ba.alleyName as "巷道名称",bl.locationCode,bl.csFloor as"层数",bl.locationName from base_location bl,base_alley ba 
where bl.warehouseId ='cbab5bf356134711a94d4b95aac80f2f'
#成品库区= 35d100c4545541a895f97825dfb95f4a  原材料= 9b2675fc97004da69d1e4f8bcf1a4e8a
and bl.whareaId =(select bw.whareaId from base_wharea bw where bw.whareaName = @kuqu)
#货位状态1：无货 2：有货  3：入库分配 4：出库分配 5：入库需盘点  6：出库需盘点
and bl.status=5
#区分里外货位 里货位原材料8个，成品16个
and bl.csPlatoon in(1,4,5,8,9,12,13,16,17,20,21,24,25,28,29,32) #成品里货位
#去掉1-3层
and bl.csFloor between 4 and 11 
#巷道
and ba.alleyId =(select alleyId from base_alley ba 
where ba.alleyName  LIKE CONCAT('%', @xiangdao, '%'))
#关联字段
and bl.alleyId = ba.alleyId
#按巷道名称排序
order by ba.alleyName

查询库存明细

# 登录数据库
use `dwms_v2.1.3_123ss`


select
仓库,
库区名称,
产品编码,
产品名称,
sum(库存) as 库存,
            
( SELECT CONCAT_WS(',',english_name,name) FROM sys_dict WHERE code=( SELECT unit FROM base_product WHERE productId = tmp.productId limit 1) and pid in (select a.id from sys_dict a where a.name='单位' and FIND_IN_SET(dept_Id,(select
        concat(REPLACE(REPLACE(pids,'[',''),']',''),id) from sys_dept where id = tmp.deptId)) order by id desc) limit 1) AS 单位,

 
    CASE 
        WHEN t2.minUnitNum >= 1 THEN TRIM(LEADING '0' FROM t2.minUnitNum)  
        ELSE t2.minUnitNum
    END       as 箱规
from (

SELECT 
                dd.deptId,
                dd.productId,
        ( SELECT warehouseName FROM base_warehouse WHERE warehouseId = dd.warehouseId limit 1) AS 仓库,
                ( SELECT whareaName FROM base_wharea WHERE whareaId = dd.whareaId limit 1) AS 库区名称,
                ( SELECT productCode FROM base_product WHERE productId = dd.productId LIMIT 1 ) AS 产品编码,
        ( SELECT productName FROM base_product WHERE productId = dd.productId limit 1) AS 产品名称,
                dd.pnum 库存,
                ( SELECT CONCAT_WS(',',english_name,name) FROM sys_dict WHERE code=( SELECT unit FROM base_product WHERE productId = dd.productId limit 1) and pid in (select a.id from sys_dict a where a.name='单位' and FIND_IN_SET(dept_Id,(select
        concat(REPLACE(REPLACE(pids,'[',''),']',''),id) from sys_dept where id = dd.deptId)) order by id desc) limit 1) AS 单位,
                ( SELECT fullname FROM sys_dept WHERE id = dd.deptId limit 1) as 组织名称,
        ( SELECT customerName FROM base_customer WHERE customerId = dd.ownerId limit 1) AS 货主,
        ( SELECT alleyName FROM base_alley WHERE alleyId = dd.alleyId limit 1) AS 巷道,
        ( SELECT locationName FROM base_location WHERE locationId = dd.locationId limit 1) AS 货位,dd.traycode,
                dd.inDate as "入库时间",dd.inventoryId
        FROM inventory_storage dd
                where dd.whareaId in ( "35d100c4545541a895f97825dfb95f4a")                
                and dd.productId not in ("ktp", "ktpd", "yclktpd", "zzx")  
                order by dd.inDate

)tmp
left join base_product t2 on tmp.productId = t2.productId
group by 产品编码



#原材料
SELECT
    ( SELECT warehouseName FROM base_warehouse WHERE warehouseId = dd.warehouseId LIMIT 1 ) AS 仓库,
    ( SELECT whareaName FROM base_wharea WHERE whareaId = dd.whareaId LIMIT 1 ) AS 库区名称,
    ( SELECT productCode FROM base_product WHERE productId = dd.productId LIMIT 1 ) AS 产品编码,
    ( SELECT productName FROM base_product WHERE productId = dd.productId LIMIT 1 ) AS 产品名称,
dd.pnum 库存,
    ( SELECT CONCAT_WS(',',english_name,name) FROM sys_dict WHERE code=( SELECT unit FROM base_product WHERE productId = dd.productId limit 1) and pid in (select a.id from sys_dict a where a.name='单位' and FIND_IN_SET(dept_Id,(select concat(REPLACE(REPLACE(pids,'[',''),']',''),id) from sys_dept where id = dd.deptId)) order by id desc) limit 1) AS 单位,
CASE 
        WHEN t2.minUnitNum >= 1 THEN
        TRIM( LEADING '0' FROM t2.minUnitNum ) 
        WHEN t2.minUnitNum = 0 THEN
        0.000 ELSE t2.minUnitNum 
    END AS 箱规,
    t2.maxPlating AS 最大组盘数,
    ( SELECT fullname FROM sys_dept WHERE id = dd.deptId LIMIT 1 ) AS 组织名称,
    ( SELECT customerName FROM base_customer WHERE customerId = dd.ownerId LIMIT 1 ) AS 货主,
    ( SELECT alleyName FROM base_alley WHERE alleyId = dd.alleyId LIMIT 1 ) AS 巷道,
    ( SELECT locationName FROM base_location WHERE locationId = dd.locationId LIMIT 1 ) AS 货位,
CASE
        ( SELECT location_sequence FROM base_location WHERE locationId = dd.locationId LIMIT 1 ) 
        WHEN 1 THEN
        "里货位" ELSE "外货位" 
    END AS 货位情况,
    dd.traycode AS 托盘条码,
    dd.inDate AS "入库时间"
FROM
    inventory_storage dd
    LEFT JOIN base_product t2 ON dd.productId = t2.productId 
WHERE
    dd.whareaId IN ( "9b2675fc97004da69d1e4f8bcf1a4e8a", "0a5de230c286447e971ed3086dedae35", "64b2530b532b4ab9b76348ad3e5124af", "73180e6969f0409baaa5b181d30c51a4", "7f656a965f564f9e9ab3d0ab8a67f3d6", "YCL1FXB", "269c2b18b6704cad97f620b9695a1eec" ) 
    AND dd.productId NOT IN ( "ktp", "ktpd", "yclktpd", "zzx" ) 
ORDER BY
    dd.inDate