MySQL 相关项目
没有别的意思就是想让大家，长长见识~ 能看懂算我输
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
Select Language