日常涨知识系列-惊天地泣鬼神的 sql， 300 行~~
資深大佬 : funbox 6
没有别的意思就是想让大家，长长见识~ 能看懂算我输
SELECT * FROM ( SELECT `inventory`.`id` AS `inventoryId`, `inventory`.`location_id` AS `locationId`, `inventory`.`storehouse_id` AS `storehouseId`, `product`.`id` AS `productId`, `product`.`name` AS `name`, `product`.`number` AS `number`, `brand`.`name` AS `brandName`, `product`.`size` AS `size`, `category`.`name` AS `categoryName`, `product`.`unit` AS `unit`, `storehouse`.`name` AS `storehouseName`, `location`.`name` AS `locationName`, IFNULL( inventory.quantity, 0 ) AS `remainQuantity`, IFNULL( inventoryBath.avgPrice, 0 ) AS `avgPrice`, IFNULL( inventoryBath.totalPrice, 0 ) AS `totalPrice`, `inventoryWarning`.`lowerLimit` AS `lowerLimit`, `inventoryWarning`.`upperLimit` AS `upperLimit`,( IFNULL( repairOrderItem.repairRemainQuantity, 0 )+ IFNULL( purchaseReturnOrderItem.purchaseReturnRemainQuantity, 0 )+ IFNULL( otherOutOrderItem.otherInOutstockQuantity, 0 )) AS `waitOutQuantity`,( IFNULL( purchaseOrderItem.purchaseRemainQuantity, 0 )+ IFNULL( otherInOrderItem.otherInOutstockQuantity, 0 )+ IFNULL( onlineInOrderItem.onlineRemainInstockQuantity, 0 )) AS `waitIntQuantity`, `product`.`barcode` AS `barcode`, `supplier`.`name` AS `supplierName`, `product`.`valid_period` AS `validPeriod`, `product`.`standard_id` AS `standardId`, `product`.`from_id` AS `fromId`, `product`.`source` AS `source`, `oeTable`.`oeNumber` AS `oeNumber`, `product`.`vehicle_note` AS `vehicleNote`, `product`.`description` AS `note`, `store`.`name` AS `storeName`, `product`.`create_time` AS `createTime`, `inventory`.`tenant_id`, `product`.`sale_price1` AS `productSalePrice`, `product`.`price_mode` AS `productPriceMode`, `product`.`markup_type` AS `productMarkupType`, IFNULL( lastPurchasePrice.lastPurchasePrice, 0 ) AS `lastPurchasePrice`,( CASE WHEN product.price_mode = 1 THEN product.sale_price1 WHEN product.price_mode = 2 AND product.markup_type = 1 AND IFNULL( inventory.quantity, 0 )= 0 THEN IFNULL( lastOutPrice.unitPrice, 0 )* product.sale_price1 WHEN product.price_mode = 2 AND product.markup_type = 1 AND IFNULL( inventory.quantity, 0 ) != 0 THEN IFNULL( inventoryBath.avgPrice, 0 )* product.sale_price1 WHEN product.price_mode = 2 AND product.markup_type != 1 THEN IFNULL( lastPurchasePrice.lastPurchasePrice, 0 )* product.sale_price1 END ) AS salePrice FROM `inventory` LEFT JOIN `product` ON `inventory`.`product_id` = `product`.`id` LEFT JOIN `category` ON `product`.`category_id` = `category`.`id` LEFT JOIN `brand` ON `product`.`brand_id` = `brand`.`id` LEFT JOIN `storehouse` ON `inventory`.`storehouse_id` = `storehouse`.`id` LEFT JOIN `location` ON `inventory`.`location_id` = `location`.`id` LEFT JOIN `supplier` ON `product`.`supplier_id` = `supplier`.`id` LEFT JOIN `store` ON `inventory`.`store_id` = `store`.`id` LEFT JOIN ( SELECT SUBSTRING_INDEX( GROUP_CONCAT( unit_price ORDER BY outstock_time DESC ), ',', 1 ) AS `unitPrice`, `product_id` AS `productId` FROM `inventory_batch` WHERE ( `store_id` = '27358' ) AND ( `inventory_batch`.`tenant_id` = 1 ) GROUP BY `product_id` ) `lastOutPrice` ON `inventory`.`product_id` = lastOutPrice.productId LEFT JOIN ( SELECT group_concat( product_number.number SEPARATOR ";" ) AS `oeNumber`, `product_number`.`product_id` AS `productId` FROM `product_number` WHERE ( `product_number`.`type` = 1 ) AND ( `product_number`.`tenant_id` = 1 ) GROUP BY `product_number`.`product_id` ) `oeTable` ON `inventory`.`product_id` = oeTable.productId LEFT JOIN ( SELECT `storehouse_id` AS `storehouseId`, SUM( quantity )- SUM( outstock_quantity ) AS `remainQuantity`, SUM( unit_price *( quantity - outstock_quantity )) AS `totalPrice`, `product_id` AS `productId`, IF ( inventoryBathAll.currentQuantity = 0, inventoryBathLast.unitPrice, round( inventoryBathAll.avgPrice, 2 )) AS `avgPrice` FROM `inventory_batch` LEFT JOIN ( SELECT `product_id` AS `productId`, SUM( quantity )- SUM( outstock_quantity ) AS `currentQuantity`, CONVERT ( SUM( unit_price *( quantity - outstock_quantity ))/( SUM( quantity )- SUM( outstock_quantity )), DECIMAL ( 20, 2 )) AS `avgPrice` FROM `inventory_batch` WHERE ( `store_id` = '27358' ) AND ( `inventory_batch`.`tenant_id` = 1 ) GROUP BY `product_id` ) `inventoryBathAll` ON `inventory_batch`.`product_id` = inventoryBathAll.productId LEFT JOIN ( SELECT SUBSTRING_INDEX( GROUP_CONCAT( unit_price ORDER BY batch_number DESC ), ",", 1 ) AS `unitPrice`, `product_id` AS `productId`, max( batch_number ) AS `batchNumber` FROM `inventory_batch` WHERE ( `store_id` = '27358' ) AND ( `inventory_batch`.`tenant_id` = 1 ) GROUP BY `product_id` ORDER BY `batch_number` DESC ) `inventoryBathLast` ON `inventory_batch`.`product_id` = inventoryBathLast.productId WHERE ( `store_id` = '27358' ) AND ( `inventory_batch`.`tenant_id` = 1 ) GROUP BY `product_id`, `storehouse_id` ) `inventoryBath` ON ( `inventory`.`product_id` = inventoryBath.productId ) AND ( `inventory`.`storehouse_id` = inventoryBath.storehouseId ) LEFT JOIN ( SELECT `product_id` AS `productId`, `lower_limit` AS `lowerLimit`, `upper_limit` AS `upperLimit` FROM `inventory_warning` WHERE ( `store_id` = '27358' ) AND ( `inventory_warning`.`tenant_id` = 1 )) `inventoryWarning` ON `inventory`.`product_id` = inventoryWarning.productId LEFT JOIN ( SELECT SUM( repair_order_product_item.quantity - repair_order_product_item.use_quantity ) AS `repairRemainQuantity`, `repair_order_product_item`.`product_id` AS `productId` FROM `repair_order_product_item` INNER JOIN ( SELECT * FROM `repair_order` WHERE ( `status` != 21000 ) AND ( `repair_order`.`store_id` = '27358' ) AND ( `repair_order`.`tenant_id` = 1 )) `repairOrder` ON `repair_order_product_item`.`order_id` = repairOrder.id INNER JOIN ( SELECT DISTINCT `product_id` FROM `inventory` WHERE ( `store_id` = '27358' ) AND ( `inventory`.`tenant_id` = 1 ) GROUP BY `product_id` ) `inventoryQue` ON `repair_order_product_item`.`product_id` = inventoryQue.product_id WHERE `repair_order_product_item`.`tenant_id` = 1 GROUP BY `repair_order_product_item`.`product_id` ) `repairOrderItem` ON `inventory`.`product_id` = repairOrderItem.productId LEFT JOIN ( SELECT SUM( purchase_return_order_item.quantity - purchase_return_order_item.outstock_quantity ) AS `purchaseReturnRemainQuantity`, `purchase_return_order_item`.`product_id` AS `productId` FROM `purchase_return_order_item` INNER JOIN ( SELECT * FROM `purchase_return_order` WHERE ( `purchase_return_order`.`status` IN ( 12000, 13000 )) AND ( `purchase_return_order`.`store_id` = '27358' ) AND ( `purchase_return_order`.`tenant_id` = 1 )) `purchaseReturnOrder` ON `purchase_return_order_item`.`order_id` = purchaseReturnOrder.id WHERE `purchase_return_order_item`.`tenant_id` = 1 GROUP BY `purchase_return_order_item`.`product_id` ) `purchaseReturnOrderItem` ON `inventory`.`product_id` = purchaseReturnOrderItem.productId LEFT JOIN ( SELECT SUM( other_in_out_stock_order_item.quantity ) AS `otherInOutstockQuantity`, `other_in_out_stock_order_item`.`product_id` AS `productId` FROM `other_in_out_stock_order_item` INNER JOIN ( SELECT * FROM `other_in_out_stock_order` WHERE ( `other_in_out_stock_order`.`type` = 1 ) AND ( `other_in_out_stock_order`.`status` = 1 ) AND ( `other_in_out_stock_order`.`store_id` = '27358' ) AND ( `other_in_out_stock_order`.`tenant_id` = 1 )) `otherInOutOrder` ON `other_in_out_stock_order_item`.`order_id` = otherInOutOrder.id WHERE `other_in_out_stock_order_item`.`tenant_id` = 1 GROUP BY `other_in_out_stock_order_item`.`product_id` ) `otherOutOrderItem` ON `inventory`.`product_id` = otherOutOrderItem.productId LEFT JOIN ( SELECT SUM( purchase_order_item.quantity - purchase_order_item.instock_quantity ) AS `purchaseRemainQuantity`, `purchase_order_item`.`product_id` AS `productId` FROM `purchase_order_item` INNER JOIN ( SELECT * FROM `purchase_order` WHERE ( `purchase_order`.`status` IN ( 12000, 13000 )) AND ( `purchase_order`.`store_id` = '27358' ) AND ( `purchase_order`.`tenant_id` = 1 )) `purchaseOrder` ON `purchase_order_item`.`order_id` = purchaseOrder.id WHERE `purchase_order_item`.`tenant_id` = 1 GROUP BY `purchase_order_item`.`product_id` ) `purchaseOrderItem` ON `inventory`.`product_id` = purchaseOrderItem.productId LEFT JOIN ( SELECT SUM( other_in_out_stock_order_item.quantity ) AS `otherInOutstockQuantity`, `other_in_out_stock_order_item`.`product_id` AS `productId` FROM `other_in_out_stock_order_item` INNER JOIN ( SELECT * FROM `other_in_out_stock_order` WHERE ( `other_in_out_stock_order`.`type` = 2 ) AND ( `other_in_out_stock_order`.`status` = 1 ) AND ( `other_in_out_stock_order`.`store_id` = '27358' ) AND ( `other_in_out_stock_order`.`tenant_id` = 1 )) `otherInOutOrder` ON `other_in_out_stock_order_item`.`order_id` = otherInOutOrder.id WHERE `other_in_out_stock_order_item`.`tenant_id` = 1 GROUP BY `other_in_out_stock_order_item`.`product_id` ) `otherInOrderItem` ON `inventory`.`product_id` = otherInOrderItem.productId LEFT JOIN ( SELECT SUM( online_purchase_order_item.quantity - online_purchase_order_item.instock_quantity ) AS `onlineRemainInstockQuantity`, `online_purchase_order_item`.`product_id` AS `productId` FROM `online_purchase_order_item` INNER JOIN ( SELECT * FROM `online_purchase_order` WHERE ( `online_purchase_order`.`status` = 1 ) AND ( `online_purchase_order`.`store_id` = '27358' ) AND ( `online_purchase_order`.`tenant_id` = 1 )) `onlineOrder` ON `online_purchase_order_item`.`order_id` = onlineOrder.id WHERE `online_purchase_order_item`.`tenant_id` = 1 GROUP BY `online_purchase_order_item`.`product_id` ) `onlineInOrderItem` ON `inventory`.`product_id` = onlineInOrderItem.productId LEFT JOIN ( SELECT `product_id` AS `productId`, `last_price` AS `lastPurchasePrice` FROM `purchase_price` WHERE ( `store_id` = '27358' ) AND ( `purchase_price`.`tenant_id` = 1 )) `lastPurchasePrice` ON `inventory`.`product_id` = lastPurchasePrice.productId WHERE ( `product`.`status` != 90 ) AND ( `inventory`.`store_id` = '27358' ) AND ( `inventory`.`tenant_id` = 1 )) `table` WHERE `table`.`tenant_id` = 1 ORDER BY `createTime` DESC LIMIT 10 大佬有話說 (100)
