庫存.sql

SELECT
	DISTINCT(PIS_InitialWarehouseAmount.GoodsSerNo),
	PIS_Warehouse.Name AS 倉庫名稱,
	PIS_Warehouse.Address AS 倉庫地址,
	PIS_Warehouse.Tel AS 倉庫電話,
	PIS_Goods.Name AS 商品名稱,
	PIS_GoodsLargeCategory.Name AS 商品大分類,
	PIS_GoodsMiddleCategory.Name AS 商品中分類,
	PIS_GoodsLittleCategory.Name AS 商品小分類,
	PIS_InitialWarehouseAmount.LastAmount AS 庫存量,
	PIS_InitialWarehouseAmountLog.SafeAmount_A AS 警戒線,
	FAS_Supplier.Name AS 供應商名稱,
	FAS_Supplier.Tel AS 供應商電話,
	FAS_Supplier.Address AS 供應商地址,
	PIS_InitialWarehouseAmount.MDT_TIME AS 修改時間
FROM
	PIS_Warehouse
	LEFT JOIN PIS_InitialWarehouseAmount ON PIS_InitialWarehouseAmount.WarehouseSerNo = PIS_Warehouse.SerNo
	LEFT JOIN PIS_Goods ON PIS_Goods.SerNo = PIS_InitialWarehouseAmount.GoodsSerNo
	LEFT JOIN PIS_InitialWarehouseAmountLog ON PIS_InitialWarehouseAmountLog.GoodsSerNo = PIS_Goods.SerNo
	LEFT JOIN FAS_Supplier ON FAS_Supplier.SerNo = PIS_Goods.MainSupplierSerNo
	LEFT JOIN PIS_GoodsLargeCategory ON PIS_GoodsLargeCategory.SerNo = PIS_Goods.LargeCategorySerNo
	LEFT JOIN PIS_GoodsMiddleCategory ON PIS_GoodsMiddleCategory.SerNo = PIS_Goods.MiddleCategorySerNo
	LEFT JOIN PIS_GoodsLittleCategory ON PIS_GoodsLittleCategory.SerNo = PIS_Goods.SmallCategorySerNo
	LEFT JOIN FAS_Corp ON FAS_Corp.SerNo = PIS_Warehouse.CorpSerNo
WHERE PIS_InitialWarehouseAmount.LastAmount > 0
ORDER BY PIS_InitialWarehouseAmount.MDT_TIME DESC, PIS_InitialWarehouseAmount.GoodsSerNo ASC