日常涨知识系列-惊天地泣鬼神的 sql， 300 行~~

没有别的意思就是想让大家，长长见识~ 能看懂算我输

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