立库常用的查询语句
关键字：
**查询有效库位**
#货位信息
#成品库区 原材料库区 #原材料巷道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