冷冻低温仓库存明细:
SELECT
CONCAT(c.Name, c.Code) AS ‘仓库名称’,
CASE
WHEN e.X4 = 0 THEN CONCAT(‘分拣区’, e.Code)
WHEN e.X4 = 1 THEN CONCAT(‘储货区’, e.Code)
WHEN e.X4 = 2 THEN CONCAT(‘隔离区’, e.Code)
ELSE ‘其他’
END AS ‘库区’,
d.Code AS ‘库位’,
f.SkuCode AS ‘商品编码’,
f.SkuName AS ‘商品名称’,
f.Barcode AS ‘商品条码’,
f.Unit AS ‘单位’,
SUBSTRING(f.Subtitle, 1, 5) AS ‘规格’,
b.Qty AS ‘库存数量’,
b.Amount AS ‘库存金额’,
CASE
WHEN a.FnType = 3 THEN ‘拆零’
WHEN a.FnType = 1 THEN ‘整件’
ELSE ‘其他’
END AS ‘分拣类型’,
CAST(b.BatchNo AS CHAR) AS ‘批次号’,
b.X14 AS ‘生产日期’,
f.ExpirationDays AS ‘保质期(天)’,
IF(
f.ExpirationDays = 0,
‘N/A’,
DATEDIFF(
DATE_ADD(
b.X14,
INTERVAL f.ExpirationDays DAY
),
CURDATE()
)
) AS ‘效期剩余天数’,
IF(
f.ExpirationDays = 0,
‘N/A’,
DATE_FORMAT(
DATE_ADD(
b.X14,
INTERVAL f.ExpirationDays DAY
),
‘%Y-%m-%d’
)
) AS ‘有效期至’,
g.no AS ‘供应商编码’,
g.Name AS ‘供应商名称’
FROM
b2b_wms.st_warestore a
LEFT JOIN b2b_wms.st_warestoreitem b ON a.Id = b.WareStoreId
LEFT JOIN b2b_wms.st_warehouse c ON a.WarehouseId = c.Id
LEFT JOIN b2b_wms.st_wareposition d ON b.WarePositionId = d.Id
LEFT JOIN b2b_wms.st_warefield e ON d.WareFieldId = e.Id
AND d.WarehouseId = e.WarehouseId
LEFT JOIN b2b_wms.st_wares f ON a.WaresId = f.Id
LEFT JOIN b2b_wms.st_supplier g ON b.SupplierId = g.Id
WHERE
a.TenantId IN (
‘3286299645965762560’,
‘3131897506372599808’
)
AND a.WarehouseId IN (
‘3370028676427628544’,
‘3353714265942151168’
)
AND b.Qty > 0;
冷冻低温仓出库明细:
SELECT
CAST(a.Id AS CHAR) AS ‘出库单号’,
CASE
WHEN a.Type = 1 THEN ‘订单’
WHEN a.Type = 2 THEN ‘参照入库出库’
WHEN a.Type = 3 THEN ‘盘亏’
WHEN a.Type = 4 THEN ‘调拨出库’
WHEN a.Type = 6 THEN ‘报损出库’
WHEN a.Type = 9 THEN ‘退供应商’
ELSE ‘其他出库’
END ‘出库类型’,
DATE_FORMAT(
a.CreateTime,
‘%Y-%m-%d %H:%i:%s’
) AS ‘出库单日期’,
IF(
a.Creator = g.UserName,
g.Name,
a.Creator
) AS ‘操作人’,
CONCAT(c.Name, c.Code) AS ‘仓库名称’,
CASE
WHEN d.X4 = 0 THEN CONCAT(‘分拣区’, d.Code)
WHEN d.X4 = 1 THEN CONCAT(‘储货区’, d.Code)
WHEN d.X4 = 2 THEN CONCAT(‘隔离区’, d.Code)
ELSE ‘其他’
END AS ‘库区’,
e.Code AS ‘库位’,
a.X3 AS ‘客户名称’,
f.SkuCode AS ‘商品编码’,
f.SkuName AS ‘商品名称’,
f.Barcode AS ‘商品条码’,
f.Unit AS ‘单位’,
SUBSTRING(f.Subtitle, 1, 5) AS ‘规格’,
b.Qty AS ‘出库数量’,
b.F8 AS ‘出库单价’,
b.F9 AS ‘出库金额’,
CAST(b.BatchNo AS CHAR) AS ‘批次’,
h.QtyReview AS ‘复核数量’,
IF(
h.ReviewUser = i.UserName,
i.Name,
h.ReviewUser
) AS ‘复核人’,
DATE_FORMAT(
h.ReviewTime,
‘%Y-%m-%d %H:%i:%s’
) AS ‘复核时间’,
DATE_FORMAT(
a.CompletionTime,
‘%Y-%m-%d %H:%i:%s’
) AS ‘完成时间’
FROM
b2b_wms.st_outstockorder a
LEFT JOIN b2b_wms.st_outstockorderitem b ON a.Id = b.OutStockOrderId
LEFT JOIN b2b_wms.st_warehouse c ON a.WarehouseId = c.Id
LEFT JOIN b2b_wms.st_warefield d ON b.WareFieldId = d.Id
LEFT JOIN b2b_wms.st_wareposition e ON b.WarePositionId = e.Id
LEFT JOIN b2b_wms.st_wares f ON b.WaresId = f.Id
LEFT JOIN b2b_wms.st_userprofile g ON a.Creator = g.UserName
LEFT JOIN b2b_wms.st_outstockorderitemhis h ON b.Id = h.OutStockOrderItemId
AND a.Id = h.OutStockOrderId
LEFT JOIN b2b_wms.st_userprofile i ON h.ReviewUser = i.UserName
WHERE
a.TenantId IN (
‘3286299645965762560’,
‘3131897506372599808’
)
AND a.WarehouseId IN (
‘3370028676427628544’,
‘3353714265942151168’
)
AND a.Status = 3
AND a.CreateTime >= @oStartTime
AND a.CreateTime <= @oEndTime;
帮我写一个商品出库频次,频率,周转等关键指数的select语句。以上提供的语句,字段waresid不在st_warestoreitem表中,逻辑错误。输出的结果需要保留商品的基本属性: CONCAT(t.`hName`, t.`hCode`) AS '仓库名称',
CASE
WHEN t.`X4` = 0 THEN CONCAT('分拣区', t.`fCode`)
WHEN t.`X4` = 1 THEN CONCAT('储货区', t.`fCode`)
WHEN t.`X4` = 2 THEN CONCAT('隔离区', t.`fCode`)
ELSE '其他'
END AS '库区',
t.`pCode` AS '库位',
t.`SkuCode` AS '商品编码',
t.`SkuName` AS '商品名称',
t.`Barcode` AS '商品条码',
t.`Unit` AS '单位',
SUBSTRING(t.`Subtitle`, 1, 5) AS '规格',
t.`Qty` AS '库存数量',
t.`Amount` AS '库存金额',
CASE
WHEN t.`FnType` = 3 THEN '拆零'
WHEN t.`FnType` = 1 THEN '整件'
ELSE '其他'
END AS '分拣类型'
最新发布