库存查询 列表SQL

#库存查询 列表SQL
SELECT
    piv.ID AS id,
    piv.INV_AMOUNT AS invAmount,
    piv.UPDATE_TIME AS updateTime,
    get_staff_name (piv.UPDATE_STAFF_ID) AS updateStaffName,
    piw. NAME AS ownerName,
    piv.OWNER_ID AS ownerId,
    piv.META_ID AS metaId,
    pim. CODE AS metaCode,
    pim.META_NAME AS metaName,
    get_lookup_code_name (pim.`META_TYPE`) AS metaType,
    get_lookup_code_name (pim.`META_CATALOG_ID`) AS metaCatalogId,
    pim.`LING_UNIT_PRICE` AS lingUnitPrice,
    get_lookup_code_name (pim.`META_UOM`) AS metaUom,
    pim.META_GWIGHT AS metaGwight,
    pim.META_LENGTH AS metaLength,
    pim.META_WIDTH AS metaWidth,
    piv.WAREHOUSE_ID AS warehouseId,
    piv.STORAGE_TYPE_ID AS storageTypeId,
    awr.`NAME` AS warehouseName,
    wst.`NAME` AS storageTypeName,
    IFNULL(piv.INV_AMOUNT, 0) / IFNULL(piv.INV_QTY, 0) AS chengben,
    IFNULL(piv.INV_QTY, 0) AS invQty,
    IFNULL(piv.COMMIT_QTY, 0) AS commitQty,
    IFNULL(piv.INV_QTY, 0) - IFNULL(piv.COMMIT_QTY, 0) AS kyCount,
    pp.onTheWayQty AS onTheWayQty
FROM
    `pi_inventory` piv
LEFT JOIN pi_owner piw ON piw.ID = piv.OWNER_ID
LEFT JOIN pi_metarials pim ON pim.ID = piv.META_ID
LEFT JOIN pi_warehouse awr ON awr.WAREHOUSE_ID = piv.WAREHOUSE_ID
LEFT JOIN pi_storage_type wst ON wst.STORAGE_TYPE_ID = piv.STORAGE_TYPE_ID
LEFT JOIN (
    SELECT
        ppi.OWNER_ID,
        ppi.WAREHOUSE_ID,
        ppi.STORAGE_TYPE_ID,
        ppil.META_ID,
        SUM(ppil.qty) AS onTheWayQty
    FROM
        pi_purchase_inout ppi
    LEFT JOIN pi_purchase_inout_list ppil ON ppil.PURCHASE_INOUT_ID = ppi.ID
    WHERE
        1 = 1
    AND ppi. STATUS NOT IN ('PI2605', 'PI2606')
    GROUP BY
        ppi.OWNER_ID,
        ppi.WAREHOUSE_ID,
        ppi.STORAGE_TYPE_ID,
        ppil.META_ID
) pp ON pp.OWNER_ID = piv.OWNER_ID
AND pp.WAREHOUSE_ID = piv.WAREHOUSE_ID
AND pp.STORAGE_TYPE_ID = piv.STORAGE_TYPE_ID
AND pp.META_ID = piv.META_ID
WHERE
    1 = 1
AND piv.STORAGE_TYPE_ID IN ('1020448090')
AND (
    piv.INV_QTY != 0
    OR piv.COMMIT_QTY != 0
    OR piv.INV_AMOUNT != 0
)
GROUP BY
    piv.WAREHOUSE_ID,
    piv.STORAGE_TYPE_ID,
    piv.OWNER_ID,
    piv.META_ID
LIMIT 500;

转载于:https://my.oschina.net/marlon520/blog/824547